When do we need to use 1 to 1 relationships in database design?

Question

When do we need to use 1 to 1 relationships in database design? In my opinion, if two tables are in a 1 to 1 ratio, they can be combined into one table. It's true?

Answer 1

• Vertical partitioning into large tables to reduce I / O and cache requirements are separate columns that are requested frequently and rarely.

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

• Supertype / subtype template .

• Vertical partitioning to exclude from the table (join) - providing the optimizer supports it (again to reduce I / O and cache).

• Anchor modeling - similar to 4, but up to 6NF .

Answer 2

This is sometimes useful for table locks. When you add a column to the database, the entire table is locked until it is completely overwritten. This has almost no effect if your database has 100 thousand rows. But if you have 100M lines or 1B lines, this is a completely different story ...

It is also useful to avoid dead lines that take up too much space. If you use MVCC and some of your columns are regularly overwritten, sometimes it makes sense to place them in a separate table. The automatic evacuation probably comes in the end, but for the sake of saving work on the hard drive, it’s better to vacuum several int fields in a separate table than a whole bunch of whole lines with text, varchar (n) and who knows what else.

The final reason could be the abuse of select * in ORM. For example, if you are storing images or articles or blog articles, it might be advisable to keep the blob / text field in a separate table. Since every time it loads for one reason or another, your ORM will load the entire line. When you only need the URL of your image or message, the last thing you want is to pull all the binary / text from the database; and yet your ORM will do just that ...

Answer 3

yes at all.

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

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

Answer 4

One reason would be to place frequently used data in one table and rarely access data in another table. It will run faster and save memory.

But I would have to twist my hand hard before I do it.