Why use a 1 to 1 relationship in database design?

Question

I'm having difficulty trying to figure out when to use a 1 to 1 relationship in db design or if it will ever be needed.

If you can only select the columns that you need in the query, there is always a point to split the table into 1 to 1 ratios. I assume that updating a large table has a greater impact on performance than a smaller table, and I'm sure that depends on how much the table is used for certain operations (read / write).

So, when designing a database schema, how do you feel about 1-to-1 relationships? What criteria do you use to determine if you need one, and what are the benefits of using one of them?

Thanks!

Answer 1

From a logical point of view, a 1: 1 relationship should always be combined into one table.

On the other hand, there may be physical considerations for such “vertical splitting” or “line splitting”, especially if you know that you will access some columns more often or differently than others, for example:

• You may want cluster or partition two 1: 1 endpoint tables in different ways.
• If your DBMS allows you to do this, you can put them on different physical disks (for example, those that are more critical for performance on SSDs and others on a cheap hard drive).
• You measured the effect on caching, and want to make sure that the "hot" columns are stored in the cache, without the "cold" columns "polluting" it.
• You need concurrency behavior (such as locking) that is "narrower" than the whole line. This is especially specific to a DBMS.
• You need different security for different columns, but your DBMS does not support column level permissions.
• Triggers are usually table dependent. Although theoretically you can have only one table and a trigger to ignore the “wrong half” of a row, some databases may place additional restrictions on what a trigger can and cannot do. For example, Oracle does not allow changing the so-called “mutating” table from a row-level trigger - having separate tables, only one of them can be mutating, so you can still change the other from your trigger (but there are other ways to get around this. )

Databases manipulate data very well, so I wouldn’t break the table only for update performance, if you didn’t fulfill the actual benchmarks for representative volumes of data and came to the conclusion that the difference in performance is actually quite significant there (for example, to compensate for the increased need for connection).

---

On the other hand, if you are talking about “1: 0 or 1” (and not about 1: 1), this is a completely different question, deserving a different answer ...

Answer 2

Separation of duties and abstraction of database tables.

If I have a user and I design a system for each user to have an address, but then I change the system, all I need to do is add a new record to the address table instead of adding a new table and transferring the data.

EDIT

At the moment, if you want to have a person record, and each person had exactly one address record, then you can have a 1 to 1 relationship between the Person table and the address table, or you can just have a Person table that also had columns for the address .

In the future, you may have decided to allow a person to have multiple addresses. You would not need to change the database structure in a 1 to 1 relationship scenario, you only need to change how you process the data you return. However, in the structure of a single table, you will need to create a new table and transfer the address data to a new table to create a one-to-many relationship database structure.

Answer 3

Well, on paper, the normalized form looks like the best. In the real world, this is usually a compromise. Most of the large systems that I know are compromise and did not try to fully normalize.

I will try to give an example. If you are in a banking application with a 10 million savings book account, and ordinary transactions will be just a request for the last balance of a specific account. You have table A, in which only information is stored (account number, account balance and name of the account holder).

Your account also has 40 more attributes, such as customer address, tax number, identifier for comparison with other systems that are in table B.

A and B have one to one mapping.

To quickly get an account balance, you can use a different index strategy (for example, a hash index) for a small table with an account balance and account holder name.

A table containing another 40 attributes can be in different table spaces or repositories, for example, a different type of indexing is used, because you want to sort them by name, account number, branch ID, etc. Your system can tolerate slow retrieval of these 40 attributes, while you need to quickly receive a request for your account balance by account number.

The presence of all 43 attributes in one table seems to be natural and probably “naturally slow” and unacceptable to simply get the balance of one account.

Answer 4

It makes sense to use 1-1 relationships to model an entity in the real world. Thus, when more objects are added to your "world", they should also relate to the data to which they refer (and no more).

To really really, your data (each table) should contain only enough data to describe the real thing that it represents, and nothing more. There should not be redundant fields, since everything makes sense from the point of view of this “thing”. This means that a smaller amount of data is repeated throughout the system (with update problems that will bring!), And that you can receive individual data independently (for example, it is not necessary to break / analyze lines).

To decide how to do this, you should examine “Database Normalization” (or “Normalization”), “Normal Form”, and “First, Second, and Third Normal Forms”. This describes how to break your data. An example version is always helpful. Perhaps try this tutorial .

Answer 5

Just a few samples from past projects:

• There can only be one corresponding report in the TestRequests table. But depending on the nature of the request, the fields in the Report can be completely different.
• in a banking project, the Entities table uses various types of objects: funds, RealEstateProperties, companies. Most of these objects have similar properties, but funds require about 120 additional fields, while they make up only 5% of the records.

Answer 6

Often people talk about a ratio of 1: 0..1 and call it 1: 1. In fact, a typical DBMS cannot support a literal 1: 1 relationship in any case.

As such, I believe it is fair to relate to the subclassification here, although this technically requires a 1: 0..1 relationship, not a literal 1: 1 concept.

A 1: 0..1 is very useful when you have fields that are the same for multiple objects / tables. For example, contact information fields, such as address, phone number, e-mail, etc., Which can be common for employees and customers, can be divided into an entity intended solely for contact information.

The contact table will contain general information such as address and phone number.

Thus, the employee table contains information about employees, such as employee number, date of hiring, etc. He will also have a link to a foreign key for the contact table for the contact information of the employee.

The client table will store information about the client, such as an email address, the name of your employer, and possibly some demographic information, such as gender and / or marital status. The client will also have a link to a foreign key for the contact table for their contact information.

Moreover, each employee will have a contact, but not every contact will have an employee. The same concept applies to customers.