6 ms·

I entirely agree. This advice is plain wrong: > Does your users table have a few columns prefixed with oidc for single sign-on authentication? Those should pro

I entirely agree. This advice is plain wrong: > Does your users table have a few columns prefixed with oidc for single sign-on authentication? Those should probably be in their own table. If your users have one set of oidc data each, leave it in the users table. There's no such thing as a 1:1 relationship.

twelve40 4y ago
sorry, what do you mean by "no such thing"? https://docs.djangoproject.com/en/4.1/topics/db/examples/one_to_one/ https://docs.djangoproject.com/en/4.1/topics/db/examples/one... yes, orms have a ton of problems, but it's clearly very much a thing.
- m000 4y ago
  Even with no ORM involved, 1-1 relations help keep things conceptually separate. This makes e.g spinning off parts of your application as microservices much easier, if the need ever arises.
  - petepete 4y ago
    How do you enforce the 1:1 relationship at the database level?
    - vasachi 4y ago
      Both tables have an ID primary key, one is also a FOREIGN KEY to the other.
      - fweimer 4y ago
        Doesn't this require support for deferrable constraints? Even if they are supported on the transaction level, once you have these kinds of dependency loops, they tend to impact lots and lots of things—restoring from backups, application-level replication, and so on.
      - adamzochowski 4y ago
        1:1 mapping means that if one table has a row, so does the other. How does this guarantee that the ID is in both tables?
        dragonwriter 4y ago
        > How does this guarantee that the ID is in both tables? Create both tables, with the same PK the second with a deferrable, initially deferred foreign key to the first. Alter the first to add a deferrable, initially deferred foreign key to the second. [Edited to correct swapped table refs in this ¶] Mission accomplished.
        qazxcvbnm 4y ago
        Something like this (adjust the primary key name convention and schema as needed): create procedure mandatory_ (_table_name "text", _mandatory_table_name "text") language plpgsql as $$ declare _trigger_name "text"; begin _trigger_name = _table_name || '_mandatory_' || _mandatory_table_name || '_trigger'; execute 'create function "' || _trigger_name || '_" () returns trigger language plpgsql as $' || '$ ' 'begin ' 'if not exists ' '( select 1 ' 'from public."' || _mandatory_table_name || '" _mandatory ' 'where _mandatory ."' || _table_name || '_id" = new ."id" ) ' 'then ' 'raise ' '''non-existent relation between "%" and "%" violates mandatory participation constraint'' ' ', ' || quote_literal (_table_name) || ', ' || quote_literal (_mandatory_table_name) || '; ' 'end if;' 'return new; ' 'end; ' '$' || '$ '; execute 'create constraint trigger "' || _trigger_name || '" ' 'after insert or update on "' || _table_name || '" ' 'deferrable initially deferred ' 'for each row execute function "' || _table_name || '_mandatory_' || _mandatory_table_name || '_trigger_" () '; end; $$;
      - petepete 4y ago
        That's a 1:N relationship. There's nothing to prevent you from adding more related records. At the database level there's only one kind of foreign key. Yeah, you can try and prevent it from happening in code or throw some unique constraints at it, but it's unnecessary.
        crgwbr 4y ago
        If the foreign key column is also that table’s primary key (or just has a unique constraint), then that’s not 1:N, it’s 1:1 or 1:0.
        petepete 4y ago
        Yeah, I noted elsewhere among the replies it's possible to use constraints to prevent extra rows in the related table, but it's inelegant and I've seen plenty of cases where it's forgotten and ends up being buggy. I'd still advise people to follow the rules of normalisation unless there are very strong to deviate.
    - giovannibonetti 4y ago
      With an unique constraint/index
      - petepete 4y ago
        Yes, I mentioned the approach elsewhere. Now you have two database things (the FK and the constraint) describing a single relationship. I don't think it's very elegant and people often forget the unique constraint. One table is a much better solution.
    - contravariant 4y ago
      If you could it would become impossible to insert any data in them.
- vbsd 4y ago
  This Django example is more of a "[one or zero] to [one or zero]" relationship, that's likely what the grandparent post means. I doubt any of the mainstream relational databases have a way to enforce exact one-to-one correspondence between two tables. You could use triggers that execute at the end of transactions or something like that - but that's not part of the relational model.
  - petepete 4y ago
    This is related to what I meant, but specifically I was referring to the inability of databases to prevent a 1:1 becoming a 1:2 (or 1:N). Yes, you can hack it with a unique constraint, but it's not very elegant.
    - crgwbr 4y ago
      That’s pretty much exactly what unique constraints are for. Why do you consider that a hack?
      - petepete 4y ago
        Mainly because you end up with something that's untidy. Your elegant table that contains all data identifiable by a single PK is now spread across two tables and relies on a (often forgotten) constraint in addition to a FK. I'd rather follow the rules of normalisation wherever possible.
        crgwbr 4y ago
        I still fail to see how your single table approach is more normalized. Consider for example an e-commerce order that may have either (1) exactly one shipping address or (2) no shipping address (for things like digital, downloadable products). You’re saying that you’d rather store the address data on the order table using a bunch of nullable fields? That bloats the order table and introduces a ton of possible consistency problems. A much more normalized model, IMO, would be to separate the address fields into their own table and use the unique FK approach to ensure that no order has more than one related address. Precisely which rules of normalization is that breaking? And how would they be resolved by putting everything into a single table?
- petepete 4y ago
  I mean at the database level. Your relationships are foreign keys and they're 1:N. Yes, you can try and artificially limit them to 1:1 using constraints but it's a hack. The correct way to represent a 1:1 relationship in a database is with one table.
ilitirit 4y ago
There are several reasons to use tables with a 1:1 relationships e.g. As described in my comment in this thread - avoiding table contention, maintainability and scalability.
deleted 4y ago
[deleted]
OJFord 4y ago
What if it can be 0 or 1 set each? So null several columns, perhaps constrain they can only be null (resp. not null) together, ... Or just have a single nullable fk to a table where the data's not nullable.
- bazoom42 4y ago
  If multiple columns can only be null/non-null together it indicates a dependency which should be normalized.
  - OJFord 4y ago
    Yes, but I was replying to 'no such thing as 1:1 relationships, just put it in the same table'.
    - bazoom42 4y ago
      But you are talking about 1:0..1 which is different from plain 1:1 relationships.
- deleted 4y ago
  [deleted]
- Akronymus 4y ago
  As much as I dislike nullable fields, I personally think that the alternative to having them (Making any nullable instead a table) can make working with it too cumbersome. > Or just have a single nullable fk to a table where the data's not nullable. Why not the other table having a non-nullable pk/fk to the main table?
  - OJFord 4y ago
    Yes or that, didn't mean to preclude that, just that 'somehow linked' table is better than bunging it all in main one IMO.

twelve40 4y ago

sorry, what do you mean by "no such thing"? https://docs.djangoproject.com/en/4.1/topics/db/examples/one_to_one/ https://docs.djangoproject.com/en/4.1/topics/db/examples/one... yes, orms have a ton of problems, but it's clearly very much a thing.

m000 4y ago
Even with no ORM involved, 1-1 relations help keep things conceptually separate. This makes e.g spinning off parts of your application as microservices much easier, if the need ever arises.
- petepete 4y ago
  How do you enforce the 1:1 relationship at the database level?
  - vasachi 4y ago
    Both tables have an ID primary key, one is also a FOREIGN KEY to the other.
    - fweimer 4y ago
      Doesn't this require support for deferrable constraints? Even if they are supported on the transaction level, once you have these kinds of dependency loops, they tend to impact lots and lots of things—restoring from backups, application-level replication, and so on.
    - adamzochowski 4y ago
      1:1 mapping means that if one table has a row, so does the other. How does this guarantee that the ID is in both tables?
      - dragonwriter 4y ago
        > How does this guarantee that the ID is in both tables? Create both tables, with the same PK the second with a deferrable, initially deferred foreign key to the first. Alter the first to add a deferrable, initially deferred foreign key to the second. [Edited to correct swapped table refs in this ¶] Mission accomplished.
        qazxcvbnm 4y ago
        Something like this (adjust the primary key name convention and schema as needed): create procedure mandatory_ (_table_name "text", _mandatory_table_name "text") language plpgsql as $$ declare _trigger_name "text"; begin _trigger_name = _table_name || '_mandatory_' || _mandatory_table_name || '_trigger'; execute 'create function "' || _trigger_name || '_" () returns trigger language plpgsql as $' || '$ ' 'begin ' 'if not exists ' '( select 1 ' 'from public."' || _mandatory_table_name || '" _mandatory ' 'where _mandatory ."' || _table_name || '_id" = new ."id" ) ' 'then ' 'raise ' '''non-existent relation between "%" and "%" violates mandatory participation constraint'' ' ', ' || quote_literal (_table_name) || ', ' || quote_literal (_mandatory_table_name) || '; ' 'end if;' 'return new; ' 'end; ' '$' || '$ '; execute 'create constraint trigger "' || _trigger_name || '" ' 'after insert or update on "' || _table_name || '" ' 'deferrable initially deferred ' 'for each row execute function "' || _table_name || '_mandatory_' || _mandatory_table_name || '_trigger_" () '; end; $$;
    - petepete 4y ago
      That's a 1:N relationship. There's nothing to prevent you from adding more related records. At the database level there's only one kind of foreign key. Yeah, you can try and prevent it from happening in code or throw some unique constraints at it, but it's unnecessary.
      - crgwbr 4y ago
        If the foreign key column is also that table’s primary key (or just has a unique constraint), then that’s not 1:N, it’s 1:1 or 1:0.
        petepete 4y ago
        Yeah, I noted elsewhere among the replies it's possible to use constraints to prevent extra rows in the related table, but it's inelegant and I've seen plenty of cases where it's forgotten and ends up being buggy. I'd still advise people to follow the rules of normalisation unless there are very strong to deviate.
  - giovannibonetti 4y ago
    With an unique constraint/index
    - petepete 4y ago
      Yes, I mentioned the approach elsewhere. Now you have two database things (the FK and the constraint) describing a single relationship. I don't think it's very elegant and people often forget the unique constraint. One table is a much better solution.
  - contravariant 4y ago
    If you could it would become impossible to insert any data in them.
vbsd 4y ago
This Django example is more of a "[one or zero] to [one or zero]" relationship, that's likely what the grandparent post means. I doubt any of the mainstream relational databases have a way to enforce exact one-to-one correspondence between two tables. You could use triggers that execute at the end of transactions or something like that - but that's not part of the relational model.
- petepete 4y ago
  This is related to what I meant, but specifically I was referring to the inability of databases to prevent a 1:1 becoming a 1:2 (or 1:N). Yes, you can hack it with a unique constraint, but it's not very elegant.
  - crgwbr 4y ago
    That’s pretty much exactly what unique constraints are for. Why do you consider that a hack?
    - petepete 4y ago
      Mainly because you end up with something that's untidy. Your elegant table that contains all data identifiable by a single PK is now spread across two tables and relies on a (often forgotten) constraint in addition to a FK. I'd rather follow the rules of normalisation wherever possible.
      - crgwbr 4y ago
        I still fail to see how your single table approach is more normalized. Consider for example an e-commerce order that may have either (1) exactly one shipping address or (2) no shipping address (for things like digital, downloadable products). You’re saying that you’d rather store the address data on the order table using a bunch of nullable fields? That bloats the order table and introduces a ton of possible consistency problems. A much more normalized model, IMO, would be to separate the address fields into their own table and use the unique FK approach to ensure that no order has more than one related address. Precisely which rules of normalization is that breaking? And how would they be resolved by putting everything into a single table?
petepete 4y ago
I mean at the database level. Your relationships are foreign keys and they're 1:N. Yes, you can try and artificially limit them to 1:1 using constraints but it's a hack. The correct way to represent a 1:1 relationship in a database is with one table.

ilitirit 4y ago

There are several reasons to use tables with a 1:1 relationships e.g. As described in my comment in this thread - avoiding table contention, maintainability and scalability.

deleted 4y ago

[deleted]

OJFord 4y ago

What if it can be 0 or 1 set each? So null several columns, perhaps constrain they can only be null (resp. not null) together, ... Or just have a single nullable fk to a table where the data's not nullable.

bazoom42 4y ago
If multiple columns can only be null/non-null together it indicates a dependency which should be normalized.
- OJFord 4y ago
  Yes, but I was replying to 'no such thing as 1:1 relationships, just put it in the same table'.
  - bazoom42 4y ago
    But you are talking about 1:0..1 which is different from plain 1:1 relationships.
deleted 4y ago
[deleted]
Akronymus 4y ago
As much as I dislike nullable fields, I personally think that the alternative to having them (Making any nullable instead a table) can make working with it too cumbersome. > Or just have a single nullable fk to a table where the data's not nullable. Why not the other table having a non-nullable pk/fk to the main table?
- OJFord 4y ago
  Yes or that, didn't mean to preclude that, just that 'somehow linked' table is better than bunging it all in main one IMO.