In a SQL database, when should a one-to-one relati

问题:

Can anyone provide some examples of when in a SQL database it's a better choice to keep one-to-one relationships on the same table, and when instead it makes more sense to have them on separate tables?

回答1:

When you have several entities which all must be able to act as a foreign key to another entity, and the "several entities" have both common properties and unique properties, and you want a NOT NULL constraint on the unique properties (or less important don't want a bunch of NULL values for the unique properties not applicable to the other entity). Even if you didn't have the unique/common properties and didn't care about the NULL values, you might still wish to do so if you wanted individual foreign constraints on each subtpye table as well as the supertype table. This strategy is called supertype/subtype modelling.

Let me give you an example.

peoples

• id (PK)
• name
• age

teachers

• id (PK, and FK to people.id)
• years_teaching NOT NULL
• whatever NOT NULL

students

• id (PK, and FK to people.id)
• grade NOT NULL
• whatever NOT NULL

As you see, teachers and students can have a single common table for some of the properties and can each have their own NOT NULL unique properties. Furthermore, you can JOIN people, teachers, and students to other tables and keep referential integrity.

Another application "might" be if you had separate databases for the each record with some of the properties in one and some in the other, however, I have never done this.