470,647 Members | 1,085 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

How to make an attribute of one table to relate with the attributes of other related table

Hello everyone,

I am a new user of postgresql... I have to create a table named person
like:

create table person (

personname varchar(40),

telefonnumber varchar(20)

);

Now, I have to create tables for personname and telefonnumber like:

create table personname (

firstname varchar(20),
lastname varchar(20)

);

and,

create table telefonnumber (

countrycode varchar(20),
areacode varchar(20),
number int4
);
Now, when the personname table has attributes:
firstname = 'John'
lastname= 'Paul'

and similarly somevalues for telefonnumber table,

i must have the value of personname attribute in person table as 'John
Paul' and the telefonnumber attribute of person table must be the
integration of attributes in the telefonnumber table.

Pls. guide me how to make this possible?...Is there any way to do so?

Thanx for spending ur valuable time and waiting for ur reply.

regards,
Jaggy Bala.
Jul 19 '05 #1
1 2171
Hello,
I am a new user of postgresql... I have to create a table named person
like:

create table person (
personname varchar(40),
telefonnumber varchar(20)
);

Now, I have to create tables for personname and telefonnumber like:

create table personname (
firstname varchar(20),
lastname varchar(20)
);

and,

create table telefonnumber (
countrycode varchar(20),
areacode varchar(20),
number int4
);

Now, when the personname table has attributes: firstname = 'John'
lastname= 'Paul'
and similarly somevalues for telefonnumber table,

i must have the value of personname attribute in person table as 'John
Paul' and the telefonnumber attribute of person table must be the
integration of attributes in the telefonnumber table.

Pls. guide me how to make this possible?...Is there any way to do so?


I don't understand why you wan't to do this that way, wouldn't it be
easier with a single table like :
create table person (
firstname varchar(20),
lastname varchar(20),
countrycode varchar(20),
areacode varchar(20),
number int4
);
?

Or, if you really want to use 3 tables, i suggest using one as an index
and others are refering to it using foreign keys. It can be useful if you
need several "telefonnumber" entries for one person.
For example :

create table person (
id serial primary key
);

create table personname (
person_id bigint references person,
firstname varchar(20),
lastname varchar(20)
);

create table telefonnumber (
person_id bigint references person,
countrycode varchar(20),
areacode varchar(20),
number int4
);

Then you can retrieve all the data for 'somebody' with something like :
select * from personname,telefonnumber
where personname.person_id = telefonnumber.person_id
and personname.lastname = 'somebody';

In this example, the table "person" is useless, since it would be better
to put the index in "personname".

Finally, if you really want to keep the tables as you describe and you
need to update the "person" table each time the "personname" or
"telefonnumber" table are updated, you can play with triggers but i don't
see any interest in this.

Hope this helps.

Bye.
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Lénaïc Huard | last post: by
6 posts views Thread by scottyman | last post: by
7 posts views Thread by Steven Bethard | last post: by
3 posts views Thread by Michael Chambers | last post: by
4 posts views Thread by MonkeeSage | last post: by
5 posts views Thread by =?Utf-8?B?UVNJRGV2ZWxvcGVy?= | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.