Should one-to-one relationships ever be split into two tables?

Question

I have one table [Users] and another table [Administrators] linked 1:0..1 . Is it best practice to merge these tables? I have read a lot of answers on SO stating splitting tables is only necessary for one-to-many relationships.

My reasoning for separating them is so I can reference administrators with AdministratorId rather than the general UserId. In other tables I have fields which should only ever contain an administrator so it acts as a referential check.

Answer 1

There is a rule of thumb that states a table either models an entity/class or the relationship between entities/classes but not both. However, it is only a rule of thumb, never say never!

SQL generally has a problem with dedicated 1:1 relationship tables because the only inter-table constraints commonly found are foreign keys. However, a FK does not require that a value exists in the referencing table. This makes the relationship 1:0..1 ("one-to-zero-or-one"), which is usually acceptable.

Strict 1:1 requires a workaround. Because SQL lacks multiple assignment, the workaround usually involves resorting to procedural code e.g. two deferrable 'bi-directional' FKs; triggers; forcing updates via CRUD stored procs; etc.

In contrast, modelling a 1:1 relationship in the same table is easy: declare both columns as NOT NULL!