1. 8

   Designing one-to-one relationships in PostgreSQL databases gajus.com

   authored by gajus 11 months ago | caches

   Archive.org Archive.today Ghostarchive

   | 5 comments
   5

1. 1. 10

      silby 11 months ago

      there is no way to tell just by looking at the query referencing the user_account_hubspot_object table that it is a one-to-one relationship

      There is often no way to tell just by looking at a query what relationships hold between tables. This is information properly held by the schema, and writing a query with the correct semantics generally requires inspecting its schema, or even (gasp) its documentation. In cases where adding columns to the primary relation is undesirable for whatever reason, a unique foreign key from the side table to the primary entity’s table is sufficient and I think your alternative solutions complicate the issue unnecessarily.

      And really, no constraints or naming schemes you add can prevent someone from doing nonsense joins.
   2. 10

      acatton edited 11 months ago

      Ouch… This looks much more complicated than it has to be.

      The only way I know to strictly model a one-to-one relationship between tables in PostgreSQL is:

      create table parent(id int primary key, foo text);
      create table child(id int primary key references parent(id), bar text);
      alter table parent
        add foreign key (id)
        references child(id) deferrable initially deferred;
      

      Of course now, you cannot insert in one table without insertin in the other, but thanks to deferred constraints you can do this in a transaction:

      begin deferrable;
      insert into parent(id, foo) values (1, 'hello');
      insert into child(id, bar) values (1, 'world');
      commit;
      

      If you try to insert in one without inserting into the other you get:

      error: insert or update on table "parent" violates foreign key constraint "parent_id_fkey"
      -- or
      error: insert or update on table "child" violates foreign key constraint "child_id_fkey"
      

      Everything else is kinda of a hack which depends on client logic.
   3. 4

      pm 11 months ago

      A bit weird that non standard practices are presented side by side with the canonical ones. With the latest not even being the first presented option.

      Relational database normalization is a well researched field with established practices well motivated by science and enginnering. https://en.wikipedia.org/wiki/Database_normalization

      If you follow normalization principles, not only you will very rarely make the wrong choice, but you also signal the intenet of your design semantically. Extending a table by placing an ID in it and adding an extra table does not mean semantically what the author is trying to do. The reasons being integraty and ocurrence of nulls. Which the author kind of hints he wants to avoid. Ultimately you will hur performance by not folowing well known and established principles.
   4. 1

      unwind 11 months ago

      Really good breakdown, in my opinion.

      It depends on a lot of things ofc, but I think you gotta ask how many users have hubspot ids. Like if only 5% have them, option A looks really bad. B-2 (& -3?) doesn’t bloat the original table at all, which seems rather nice. I’d say B-2 & B-4 are my favorites.
   5. 0

      mxey 11 months ago

      Another reason to use separate tables is if the fields are modified by different transactions/clients: even if a transaction only changes one field in a row, the whole row will be locked, and the whole row will be written out.

🇬🇧 The UK geoblock is lifted, hopefully permanently.