The answer is mechanically derived from the idea of functional dependence.
For a value to exist in one relation, it implies that a value must exist in the other. When this is true, there will be a foreign key constraint from the dependent table (the former) to the independent table (the latter)
Another way of looking at this is that a one to one relationship is actually just a special case of a one to many relationship; only instead of many, you are only allowed one.
in SQL:
CREATE TABLE independent (
id INTEGER PRIMARY KEY
);
CREATE TABLE dependent (
independent_id INTEGER UNIQUE NOT NULL FOREIGN KEY REFERENCES independent(id)
);
Like a one to many, the 'many' has a foreign key to the 'one', but to turn the 'many' into a 'one', just make it unique
. It's typically convenient to express all of this by making the foreign key column on the dependent relation the primary key for that relation:
CREATE TABLE dependent (
independent_id INTEGER PRIMARY KEY FOREIGN KEY REFERENCES independent(id)
);
Edit: I noticed your title asks a different question than your body seems to ask. The above answers the title.
From the point of view of database normalization, it's probably preferred to use multiple tables, as above, in favor of nullable attributes. Nulls are sort of an out of band way of saying that the value of a particular attribute is in some way 'special', but doesn't really enforce any particular interpretation of what that might mean. A null manager_id
probably means something totally different from a null birthdate
, even though they have the same mark.
Adding tables isn't considered in any way a bad thing, from a strictly abstract or academic point; neither is adding attributes. The choice should always be based on what kind of data you actually need to model.
That said, there are some very real practical reasons to use one or the other. The most obvious performance reason comes from the space cost of using one or the other. When an optional value is usually used, the extra space used by the foreign key and corresponding index doesn't pay for itself to well. Similarly, if an optional value is rarely used; it's more compact to put those values in another relation. Having a nullable attribute would consume space in the table that is hardly ever used.
Figuring out which basically requires actual data, and performance testing these (and maybe other) configurations to see which works best.