Thread

inheritance question

Eric J McKeown <ericm@palaver.net> — 1998-08-03T22:33:46Z

I have a question about Postgres inheritance.

I have a table called people that will contain certain basic
characteristics for people such as first name, last name, address, etc.
This table also has a "p_id" field that is the primary key.

Now, I've declared several subclasses for this table, such as "employees"
and "citizens", in order to place people into separate categories, and, in
some cases, to provide extended sets of information for specific
subclasses of people.  The table structure for the superclass, people, and
the two subclasses, employees and citizens, is shown below.

Table    = people
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| p_id                             | varchar() not null               |
24 |
| fname                            | varchar() not null               |
32 |
| lname                            | varchar() not null               |
32 |
| phone                            | varchar()                        |
12 |
| fax                              | varchar()                        |
12 |
| street                           | varchar()                        |
32 |
| city                             | varchar()                        |
32 |
| state                            | varchar()                        |
2 |
| zip                              | varchar()                        |
10 |
+----------------------------------+----------------------------------+-------+

Table    = citizens
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| p_id                             | varchar() not null               |
24 |
| fname                            | varchar() not null               |
32 |
| lname                            | varchar() not null               |
32 |
| phone                            | varchar()                        |
12 |
| fax                              | varchar()                        |
12 |
| street                           | varchar()                        |
32 |
| city                             | varchar()                        |
32 |
| state                            | varchar()                        |
2 |
| zip                              | varchar()                        |
10 |
+----------------------------------+----------------------------------+-------+

Table    = employees
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| p_id                             | varchar() not null               |
24 |
| fname                            | varchar() not null               |
32 |
| lname                            | varchar() not null               |
32 |
| phone                            | varchar()                        |
12 |
| fax                              | varchar()                        |
12 |
| street                           | varchar()                        |
32 |
| city                             | varchar()                        |
32 |
| state                            | varchar()                        |
2 |
| zip                              | varchar()                        |
10 |
| division                         | int4 not null                    |
4 |
| title                            | varchar() not null               |
80 |
| image                            | varchar()                        |
3 |
| bio                              | text                             |
var |
+----------------------------------+----------------------------------+-------+

I'm having trouble getting that to lay out properly, so I hope it's not
too difficult to read.

Anyway, my problem is this.  I want to have some people be members of the
employees and citizens classes simultaneously, but I don't want to
duplicate information for those people.  That is, if an individual person
fits into both the citizens table and the employees table, I want that
person to have only one p_id and one set of information for address, etc.
Is there an easy way to accomplish this?  Is this feasible?  I want to
make sure that I won't have to update multiple tables when updating a
person's address or phone number, etc.  Can this be done?

I hope my questions aren't too vague.  I'm starting to get some ideas for
accomplishing these things as I write this message, but I'd like to hear
what others think.

Thanks...

eric
_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net