sqlpostgresqldatabase-designone-to-one

PostgreSQL - One-To-One - which approach is "better"?


I want to create a One-To-One relationship between two tables : teachers and office (the teacher is the owner of the relationship). Looking up online, I found two different approaches :

First one, to define a foreign key in the office table and add constraints NOT NULL and UNIQUE. Basically, a mandatory One-To-Many relationship with the UNIQUE constraint: Like so :

CREATE TABLE office(
    id SERIAL PRIMARY KEY,
    floor SMALLINT CHECK (floor > 0 AND floor <= 10),
    size SMALLINT CHECK (size > 0),
    teacher_id INTEGER NOT NULL UNIQUE REFERENCES teachers(teacher_id)
);

And the second one where we combine the primary key and the foreign key in the office table into one row. Like so :

CREATE TABLE office(
    id SERIAL PRIMARY KEY REFERENCES teachers(teacher_id),
    floor SMALLINT CHECK (floor > 0 AND floor <= 10),
    size SMALLINT CHECK (size > 0)
);

Functionally, it looks to me like both approaches work the same. The first one seems to use a little bit of extra memory, since it has one more row.

My question is: What are the advantages and disadvantages of each approach? Is one approach "better" than the other ?


Solution

  • I would go with the second approach, to share the exact same value for the primary key. For the office table the primary key should not be autogenerated (serial) but just an int.

    For example you can do:

    create table teachers (
      id int primary key not null,
      name varchar(20) not null
    );
    
    create table office (
      id int primary key references teachers (id),
      floor smallint check (floor > 0 and floor <= 10),
      size smallint check (size > 0)
    );
    
    alter table teachers
    add constraint fk_uq1_teachers_office
    foreign key (id) references office (id) deferrable initially deferred;
    

    Then inserting data could look like:

    begin transaction;
    
    insert into teachers (id, name) values (100, 'Mary');
    
    insert into office (id, floor, size) values (100, 3, 850);
    
    commit;
    

    Notice that in order to enforce the 1-1 relationship you need to have two foreign keys: one from teachers to office, and one from office to teachers. Otherwise an application could insert data into teachers and forget to insert into office. Fortunately you are using PostgreSQL that implements the standard SQL feature of constraint deferrability as shown above.

    See running example at DB Fiddle.