Tables with a common primary key

Question

What's the term describing the relationship between tables that share a common primary key?

Here's an example:

Table 1
property(property_id, property_location, property_price, ...);

Table 2
flat(property_id, flat_floor, flat_bedroom_count, ...);

Answer 1

"Foreign key"?

Answer 2

What you have looks like table inheritance. If your table structure is that all flat records represent a single property but not all property records refer to a flat, then that's table inheritance. It's a way of modeling something close to object-oriented relationships (in other words, flat inherits from property) in a relational database.

Answer 3

If I understand your example correctly, the data modeling term is Supertype/Subtype. This is a modeling technique where you define a root table (the supertype) containing common attributes, and one or more referencing tables (subtypes) that contain varying attributes based on the entities being modeled.

For example, you could have a Person table (the supertype) containing columns for attributes pertaining to all people, such as Name. You could then have an Employee table (the subtype) containing attributes specific to employees only, such as rate of pay and hire date. You could then continue this process with additional tables for other specializations of Person, such as Contractor. Each of the subtype tables would have a PersonID key column, which could be the primary key of the subtype table, as well as a foreign key referencing the Person table.

For additional info, search Google for "supertype and subtype entities", and see the links below.

• http://www.learndatamodeling.com/dm_super_type.htm
• http://technet.microsoft.com/en-us/library/cc505839.aspx

Answer 4

There isn't a good name for this relationship in common database terminology (as far as I know). It's not a one-to-one relationship because there isn't guaranteed to be a record in the "extending" table for each record in the main table. It's not a one-to-many relationship because there a maximum of one record allowed on what would otherwise be the "many" side of the relationship.

The best I can do is a one-to-one-or-none or a one-to-one-at-most relationship. (I will admit to sloppy terminology myself — I just call it a one-to-one relationship.)

Whatever you decide to call it, you can model it properly and maintain integrity in your database by making the property_id column in property a PK and the property_id column in flat a PK and also an FK back to property.

Answer 5

"Logic and Databases" advances the term "at most one to at most one" for this kind of relationship. (Note that it is insane to assign names to tables on account of which relationships they participate in.)

Beware of the people who have suggested things like "foreign key", "table inheritance", brief, all the other answers given here. Those people are making assumptions that you have not explicitly stated to be valid, namely that one of your two tables will be guaranteed to contain all key values that appear in the other.

Answer 6

(Disfunctionality of the site prevents me from adding this as a comment in the proper place.)

"How would you interpret "...that share a common primary key?" "

I interpret that in the only reasonable sense possible: that within table1, attribute values for the primary key are guaranteed to be unique, and that within table2, attribute values for the primary key are guaranteed to be unique. And that furthermore, the primary key in both tables has the same [set of] attribute names, and that the types corresponding to the primary key attribute[s] are also pairwise the same. Nothing more and nothing less.

In particular, "sharing a primary key" means "having a primary key in common", and that means in turn "having a certain 'internal uniqueness rule' in common", but that commonality guarantees in no way that a primary key value appearing in one table must also appear in the second table.

"Can you give an example involving two tables with shared primary keys where one table wouldn't contain all the key values that appear in the other?" "

Table1: column A of type integer, primary key A Table2: column A of type integer, primary key A

Rows in table1: {A:1}. Satisfies the primary key for table1. Rows in table2: {A:2}. Satisfies the primary key for table2.

Convinced ?