Confusion in a 1: 1 ratio

Question

I am studying database design and I am confused by the 1: 1 relationship. As far as I understand, you can just add columns to the corresponding table. Can anyone provide a real example of where a 1: 1 ratio was either necessary or provided some significant benefit? Ie, where would I use a 1: 1 ratio and what would it look like?

Answer 1

I will give you a real practical example.

In the world of medical billing, doctors who want to get paid with medication process the billing, creating a dictation report for each visit with the patient. In fact, it can be a recorded sound dictation written by the secretary, but most often it is just a written description of what they did and talked with the patient, as well as history, impressions, etc. Then the licensed medical coder will read this dictation and determine what to allow the doctor.

Unlike dictation, there is demographic information about the patient involved: name, age, billing address, etc. This information should be strictly separated from dictation information so that encoders do not allow prejudice to cloud their evaluation decisions or violate patient confidentiality.

This data is often well normalized with a ratio of 1: many in data systems at the point of origin, and only the right parts are displayed to the right people at the right time. However, a significant number of branches have introduced their billing function to third parties. Thus, a small clinic, for example, should not contain a licensed medical coder for staff; one coder in the billing department can cope with the needs of many clinics. When data is sent from the clinic to the billing department, the patient’s demographic information and dictations should come as separate parts, possibly at different times. At the moment, they are most likely to be stored in completely separate tables with a 1: 1 ratio and a common identifier field for their subsequent correspondence.

In this case, the 1: 1 ratio is very little related to the data model. You could probably match the records during the import, and as the bill moves through the system, the provincial patient information obtained in the demographic record in the clinic will be matched with the real person, so the relationship is 1: many can be restored. Otherwise, you will receive a separate expression on a separate account for each visit to the doctor.

Instead, it has almost everything related to system design. Probably completely different people build and use the billing part, styling the coding part in our imaginary payment service. Thus, each party can each have full control over their own patrimony, and you are sure that no one, not even the developer, violates any privacy rules.

Answer 2

True one-to-one relationships rarely occur in the real world. This type of relationship is often created to get around some limitation of the database management software, rather than a model of the real situation. In Microsoft Access, a one-to-one relationship may be necessary when you need to split tables into two or more tables, because of security or performance problems or because of a limit of 255 columns per table. For example, you can save most of the patient information in tblPatient, but especially sensitive information (for example, patient name, social security number and address) in tblConfidential (see Figure 3). Access to information in tblConfidential may be more limited than for tblPatient. As a second example, perhaps you need to transfer only part of a large table to another application on a permanent basis. You can split the table into transmitted and non-transmitted parts and join them in a one-to-one ratio.

What a quote from here: The Basics of a Relational Database Structure

And here is a similar question to SO.

Another reason I can see the use of 1: 1 (where I used it in the past) is if you have a table with a large number of columns, and only a few of them are associated with very intensive and frequent queries, which should be fast , I would split it into two tables that are related to 1: 1, where I could query a lightweight table and get good performance, but still have other data related to it, easily with a simple join.

Answer 3

I belief tables should be designed using the domain background. Therefore, if these columns form two different objects, they cannot be mixed in the same table. In my experience, 1: 1 ratios tend to develop in a 1: n ratio over time.

For example, you can save a person’s mailing address. But after a while you should keep more than one address per person. Refactoring programs from a 1: 1 to 1: n ratio is usually much simpler than extracting some columns from an old table to a new one.

Many database systems make it very easy to determine access permissions on a table. But defining permissions on individual columns is often quite painful.

Answer 4

It is useful if X has a ratio of 1: 1 with Y and Z also has a ratio of 1: 1 with Y. Y can be abstracted into a common table, rather than duplicated in both X and Z.

EDIT: An example for the real world will be customers, companies and addresses. There may be an N: N connection between the Client and the Company. But the Client and the Company have a 1: 1 relationship with the Address. Some address lines can be associated with both the Client and the Person.

Answer 5

First, because they talk about Access (Jet, Ace, whatever) - @Richard DesLonde's credit for defining this - then they probably talk about a 1: 0..1 relationship. I do not believe that true 1: 1 relationships work in Access because it does not have a mechanism to defer constraints or execute multiple statements in SQL PROCEDURE . Most access practitioners are satisfied that they use the 1: 0..1 ratio to model the true 1: 1 relationship, so I think the authors are satisfied that they use the term “1: 1” informally to refer to both.

Of course, the ratios 1: 1 and 1: 1..0 are quite common in the real world. I rather think that they are trying to convey the (valid) point that some 1: 1 and 1: 1..0 relationships are invented in the data model for business purposes.

Consider the “natural person” (ie person) and the “corporation”. They do not have common attributes (of course, both have a “name”, but their domains are different, for example, “natural person’s name” has subatomic domains for “last name”, “first name” and “name”, etc.).

However, in this data model, different types of objects can play the same role. For example, an “individual” and a “corporation” may be officers of a “corporation”. In the data model, we could have two different types of entities: "employees of an individual" and "corporate employees", which can have many common attributes from the same domains, for example. date of appointment, end date, etc .; in addition, these business rules will be the same, for example, the appointment date must be before the end date. In addition, both will engage in equivalent relationships, for example. “natural person representing” etc.

The data model can be “split up” at a high level, which leads to pairs of very similar tables, for example. “individual” and “corporate employee”, “individual representing an individual”, and “representative of a corporate representative representing an individual”, etc.

However, another approach is to model common attributes and relationships using a fabricated object type. For example, both an “individual” and a “corporation” can be regarded as a “legal entity” (aside: there is such a concept of a “legal entity” in the law, but does this mean the same thing as existing in the real world ?!)

Consequently, we could have a superclass table for "legal entities" and subtypes for "individuals" and "corporations" respectively. The table of "officers" will refer to the table of "legal entities". All subsequent relationship tables may refer to the officers table, which will reduce the number of tables from this point down.

There are practical problems for this subclass approach. Since the “natural person” and the “corporation” do not have common attributes, they do not have a common key, therefore, for the table “legal entities” there should be an artificial key with all the problems that arise from this, especially if it needs to be exposed in the application. In addition, since the relationship between “legal entities”, “individuals” and “corporations” is indeed 1: 1, some DBMSs, like Access, will not have the necessary functionality for their effective implementation, and many of them will have to agree to do There are 1: 0..1.

Answer 6

The 1: 1 ratio is an abstract concept that you model in your data, but at the database level (assuming RDBMS) does not actually exist. You always have a foreign key on one table pointing to another, so technically the parent table that FK points to can have several children. This is what you want to apply in your business logic.

A good example of a 1: 1 ratio in terms of modeling would be the ratio between employee and person. You have a person with certain data, then you have additional attributes for the same person that you put on the employee. A good way to think about this in terms of OO programming is with inherited classes. The Employee class, inherited from Person. In fact, ORM systems can model a 1: 1 ratio in a database with each table having a common primary key.