CODEWITHSUNDEEP
sqldatabase
William
William

Reputation: 386

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

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.

Upvotes: 6

Views: 4227

Answers (5)

Ritveak
Ritveak

Reputation: 3768

Let me stick to the title of your Question first.

Yes, One to one relationship can be split into different tables when you need the following:

  1. Modularity
  2. Security / Data Abstraction

I guess the modularity bit is quite clear.

The Security bit is obvious too since you will require access to both the tables to fetch the whole picture of data.

For Example, Let's suppose you have the below as the scenario:

Customer and Passport are two different tables. Wherein, Customer has a dummy id for passport and Passport table doesn't have corresponding customer info. Hence, the person with access to both tables can only map the passport id with the customer and see the whole picture.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146459

There are several reasons why you might want to keep them separate. One is if the records in one table represent a subset of the records in the other. This patterns is called sub-classing, and is clearly the case in your situation.
This is wise even if the fields (the data) you need to store about admins are not different from the data you need to store about all users. Another reason is if the the usage patterns for a few columns is very different (greater frequency of access) from the usage patterns for the rest of the columns.

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57023

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!

Upvotes: 7

HLGEM
HLGEM

Reputation: 96552

It is common to have tables in a one-to-one relationship. First if they are separate entities that will need to be queried separately or if they are subclasses (as in your case) then the separate table makes sense. Also if the primary table is getting too large for the maximum record size it makes sense to have an additional table in a one-to-one relationship. Finally you have the case where the relationship is 1-1 now, but has the potential to be 1-many in the future such as when you only have one phone number now but wil probably need to store more later. In this case it will be less work to go ahead and make it a separate table.

The crtical piece to setting up a 1-1 relationship is to enforce it as 1-1. The easiest way to do this is to make the FK field also the PK field in the second table.

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I think the best option is to have two tables for the two different entities, Users and Administrators, possibly with the same Primary Key.

CREATE TABLE User
( UserId int
, ... other data               --- data for all users
, PRIMARY KEY (UserId)
) ;

CREATE TABLE Administrator
( AdministratorId int
, ... other data               --- data for administrators only
, PRIMARY KEY (AdministratorId)
, FOREIGN KEY AdministratorId
    REFERENCES User(UserId)
) ;

This way, as you mention, other tables can reference the AdministratorId:

CREATE TABLE OtherTable
( OtherTableId int
, AdministratorId int
, ... other data
, ...
, FOREIGN KEY AdministratorId
    REFERENCES Administrator(AdministratorId)
) ;

Benefits:

  • referential integrity is trivially implemented.
  • the relevant data (for Users and Admins) can be stored in the relevant tables so you have less columns in the tables and fewer NULL data.

  • any query that needs a JOIN to Administrator table will have to look up only a few rows, compared to the (possibly huge) number of rows of the User table. If you have only one table, you'll end up with code like:

    WHERE User.admin = True

    which may not be easily optimized.

Upvotes: 4

Related Questions