Re: On the subject of inheritance - Mailing list pgsql-novice

Martin Foster <martin@ethereal-realms.org> writes:
> I have a table which is used throughout the site and tracks users.
> This information is what is required by the scripts to establish
> identity, authenticate and correspond with off site.  e.g.

> USER
>    UserName  VARCHAR(10)
>    UserPass  VARCHAR(12)
>    UserEmail VARCHAR(30)

> There is a new aspect to the site which is completely optional and seems
> like a good candidate for inheritance of the user table.    Simply put,
> these additional attributes ADDITIONAL would be in a separate table
> applied to and used only when necessary.   This brings me to my questions.

> OPTIONAL (Inherits from USER)
>    OptionalFirstName VARCHAR(15)
>    OptionalLastName  VARCHAR(15)
>    OptionalAddress   VARCHAR(45)
>    OptionalPhone     VARCHAR(15)

> First of all, does a row created in the USER table appear in the
> OPTIONAL table which inherits from it?   Meaning could I add in the
> optional attributes at a later time using a simply ALTER TABLE OPTIONAL
> statement when the UserName exists in USER?

I think what you really want is just to add these columns to USER and
allow them to be NULL when you don't have values for them.  They won't
take up any material amount of space when they are NULL, so you need not
worry about that.

The problem with trying to do this with inheritance is that a row in
OPTIONAL is not some sort of implicit extension of a matching row in
USER, it is a separate full-fledged entity.  Thus, you'd be dealing with
having to actually move rows from USER to OPTIONAL or vice versa
depending on whether you had these values for that user or not.  That
seems like useless complication of your application logic.

It may help you to explain that an inherited table is physically
completely separate from the parent, and contains its own complete rows
including all the columns derived from the parent as well as any locally
added columns.  The only "magic" is that a query that scans the parent
table is automatically modified by the system to scan the child table(s)
as well, so that the results include both parent and child rows.  Of
course, the query can't refer to any non-inherited columns of the child,
since it has no way to name them.

> Would I get a referential integrity error

You would not, because there isn't any reference from OPTIONAL to USER.
Ideally, what you'd get if you put the same UserName into both tables
is a unique-key violation.  At the moment, we don't have any way of
enforcing uniqueness across multiple physical tables, and so the result
would just be wrong :-(.  This is what the manual is talking about when
it says that inheritance doesn't currently play nicely with unique (or
foreign-key) constraints.

            regards, tom lane