modeling a "one-to-one" relation in a database is the same than modeling "inheritance"?

Question

Hi,

i was wondering if there's a difference between modeling a one-to-one relation and a inheritance relation (Table-Per-Type) In both cases we add a foreing key with a UNIQUE constraint, or somethimes the FK is part of the PK

It seems to me that the table structure is the same, but maybe i'm missing something.

Thanks in advance.

Answer 1

When it comes to modelling, a one-to-one relationship between entities indicates that they are the same relational table. "The key, the whole key and nothing but the key, so help me Codd!"

Answer 2

There are a few permutations of this. Here are some: - Suppose A can exist only with B and B can can exist only with A. Then the relationship is one to one. - Suppose A can exist alone and B can extend it, but B cannot exist alone. Then the relationship is inheritance. - Suppose A cannot exist alone, but it can exist with either B or C. Then the relationship is inheritance.

Answer 3

This is merely a rewording of what others are answering, but I always say the difference is in not in the table structure (which is indeed the same), but in the cardinality constraints on the foreign key:

• in both cases, you have a table T1 with a foreign key F "pointing to" (i.e. containing values from) a key P of another table, T2;
• in both cases, every F points to a different P (P and F are both UNIQUE);
• in both cases, every F actually points to a P (P and F are both non-NULL);
• in case of inheritance, not every P always occurs as a value of F;
• in case of a one-to-one correspondence, every P always occurs as a value of F.