CODEWITHSUNDEEP
sqldatabaseforeign-keysrelational-database
Pensierinmusica
Pensierinmusica

Reputation: 6950

In a SQL database, when should a one-to-one relationship be in the same table and when in separate tables?

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?

Upvotes: 6

Views: 1437

Answers (1)

user1032531
user1032531

Reputation: 26281

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.

Upvotes: 2

Related Questions