When we need to use 1-to-1 relationship in database design?

Question

When do we need to use a 1-to-1 relationship in database design? In my opinion, if two tables are in a 1-to-1 relationship, they can be combined into one table. Is this true?

Answer 1

1. Vertical partitioning for large tables to reduce I/O and cache requirements -- separate columns that are queried often vs rarely.

2. Adding a column to a production system when the alter table is "too expensive".

3. Super-type/subtype pattern.

4. Vertical partitioning to benefit from table (join) elimination -- providing optimizer supports it (again to reduce I/O and cache) .

5. Anchor modeling -- similar to 4, but down to 6NF.

Answer 2

On occasion it's useful for table locks. When you add a column to a database, the whole table gets locked until it's fully rewritten. This has little to no impact when your database has 100k rows. But if you've 100M rows, or 1B rows, it's a whole different story...

It's also useful to avoid dead rows that take too much space. If you're using MVCC and some of your columns are regularly overwritten, it occasionally makes sense to place them in a separate table. Arguably, auto-vacuuming eventually kicks in, but for the sake of sparing hard drive work, better vacuum a few int fields in a separate table than a whole bunch of entire rows full of text, varchar(n) and who know what else.

A last reason would be the abuse of select * in ORMs. If you're storing images or blog posts/articles, for instance, it may make sense to store the blob/text field in a separate table. Because every time it gets loaded for a reason or the other, your ORM is going to load the whole row. When you only need the URL of your image or post, the last thing you want is to pull the whole binary/text from the database; and yet your ORM will do just that...

Answer 3

yes in general.

One exception may be if you want to assign privileges differently to a subset of columns.

also consider that this is true only when both sides are required.

Answer 4

One reason would be to put frequently accessed data in one table and extremely rarely accessed data in another table. It would run faster and save some memory.

But I would have to have my arm twisted hard before I would do it.

Answer 5

One-to-one is ambiguous, because one refers to a cardinality and does not explicitly clarifies if it is a mandatory participation in the relationship (i.e.one and exactly one) or an optional participation (i.e. zero or one).

If it is "exactly-one-to-exactly-one", you could indeed in practice decide to merge both design entities in a single table implementing both. Such an approach is equivalent from the point of view of the data it can represent and can sometimes simplify the implementation. The technical pros and cons are well described in the other answers. It is however not completely equivalent to the initial design:

• The separation of concerns between the two entities is broken. This may have some drawbacks, for example if the concepts behind each entity may evolve differently (e.g. different applications are in charge of their maintenance) or have different requirements (e.g. authorisations) .
• From the theoretical perspective, entities should by definition have their own independent identity, which is implemented at database level with a primary key. Merging would lead to two different potential primary keys, one of which being functionally dependent of the other: this is not recommended in view of normalisation principles. Using the same primary key for both identities easily solves this problem, but means that entities have no longer an independent identity. It's a subtle semantic difference that you can ignore if the advantages outweigh the inconveniences

If it is any combination involving zero-or-one, then you should avoid merging the two entities in a single table:

• Technically you could, but this would require the attributes of the optional entity nullable. In the second example above, this would mean that all the columns except the primary key would be nullable, which no longer allow to enforce consistency, e.g. if some are mandatory .
• Keeping them separate is perhaps a small overhead because you have one more table, but represents accurately at db level all possible cases.