Hacker News Reader

Comment by petepete | Hacker News Reader

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.

View on HN

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.

If multiple columns can only be null/non-null together it indicates a dependency which should be normalized.

Yes, but I was replying to 'no such thing as 1:1 relationships, just put it in the same table'.

But you are talking about 1:0..1 which is different from plain 1:1 relationships.

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?

Yes or that, didn't mean to preclude that, just that 'somehow linked' table is better than bunging it all in main one IMO.

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.

sorry, what do you mean by "no such thing"?

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.

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.

How do you enforce the 1:1 relationship at the database level?

Both tables have an ID primary key, one is also a FOREIGN KEY to the other.

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.

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?

> 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.

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;
        $$;

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.

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.

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.

With an unique constraint/index

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.

If you could it would become impossible to insert any data in them.

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.

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.

That’s pretty much exactly what unique constraints are for. Why do you consider that a hack?

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.

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?

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.

Reply on news.ycombinator.com