What is the advantage of a 1:1 relationship?

Asked

Viewed 1,573 times

5

I have a database (Mysql), and in a table I’m having about 80 columns. I am thinking of separating this information into other tables, and maintaining a 1:1 relationship. But this can impact a little on development time, create more objects, etc.

What is the advantage of using several tables with 1:1 relationship, instead of using everything in the same table?

  • 2

    You have no anomalies with update/delete or information loss, a database table is also not an xD excel spreadsheet. Super recommended reading => What is database normalization?

  • 1

    For example if in the table funcionario has a column with the description of cargo let’s say that when the last intern is promoted or fired (delete in this case) no other employee can have this position because he no longer exists. Now if in place of the job description you had the job id that event would not cause any loss of information.

  • I get it, and can I get anything out of performance? Being that in 95% of the cases where I perform query in this table, I will not bring all the data, but only specific data.

  • 1

    Yes and no. Yes pq breaking into several tables will eliminate duplicated values which results in fewer rows in a table or if you need something specific just takes the necessary. Not performace generally diminu pq will need some joins to bring all the information needed by the query since now they are separate.

2 answers

9


In the commentary I think you are talking about the relationship N:1. Many people confuse it with 1:1. Unless the text is just confused because of the short maximum space imposed.

The N:1 is because of the normalization, there the example eventually becomes 1:1, but by circumstance, not by obligation, is not formally 1:1. In this case, isolating the data you can do maintenance on it and this is reflected everywhere it was used. If it is on the table itself, it becomes repetitive and a maintenance on it has to find out everywhere it was used and change everything. And you’re still running the risk of it being misinterpreted in a non-standard way and you can’t even identify where it was used. Normalization creates a canonical way to deal with it. Many people don’t realize that this is DRY, probably the concept that if one totally dominates not need to learn as well others.

Often we look at 1:(0,1) as 1:1. The 1:1 even though obliging the two sides to have 1, in some cases it may be that one side must have one, but the other may have zero.

1:1 is usually used for some reasons:

  • The domain (object) is totally different, and the two sides can only have one.

    One example I give a lot is the person who has details of their role as customer, supplier, employee, etc. The person can only have a given of each of these papers (each paper has its table) and each paper registration can only belong to one person. This is clearly 1:1, or 1:(0,1) since most of the time a person may not have a specific role. Many people don’t see it that way because they duplicate entities in the database or do tables/classes "gods". Most models out there are copies of what already existed, one doesn’t think about it, and does wrong.

    Other examples 1:1 or 1:(0,1):

    One person picks up a cell phone from the company. One person can only take one cell phone at a time (company rule) and this cell phone is only with one person (it can have 0 cell phones). But if you want to keep cell phone history already caught the model no longer works.

    There is only one head in the department and only one head manages a department (some rule may be different, but we will treat that always is so in this company). But if any rule changes, it’s gone

    A person only has a current spouse (has a country that is not like this).

    A guest can only book a room at the hotel and the room can only be reserved for one customer (whereas it is not done overbooking).

    Wikipedia has more (the example of the capital has exception, although you can circumvent it and leave 1:1). That is, all work if the rule is too rigid.

  • Many data are optional, in theory one can go up to the sixth normal way to solve this. A lot of times when this is happening, it’s because maybe the domain was different and the person didn’t understand.

    If people knew how to use the right relational model and Sgdbrs had put some features before maybe there would be no Norel (who people call Nosql).

  • Secondary parts of a table/object data are rarely accessed together with the main part. So it is an optimization to keep separate and not have to touch (read or write) on these parts (helps the cache and reduces the automatic locking area in competition and may have fewer contacts with the permanent mass storage, which is slow, in long imports). This optimization needs to be very well thought out because it can often be worse than expected. It can also help if the person abuses the SELECT *, but it’s the solution to a problem that shouldn’t even exist.

    What is faster depends on a number of factors that we often cannot anticipate before the system is used effectively. And we often optimize things that they didn’t need or that get worse than we expected. Almost always do this before you know you need it is premature optimization.

    Usually people ignore logical modeling. People always think about physical modeling. It is possible to make the logical modeling in the most organized way and the physics in the most optimized way.

    Many people use it thinking about it, but in the background the data were of a different domain and the real reason that the person used, without realizing, is the previous.

  • Some cases have so many columns that it’s almost unmanageable that way. It divides, but again, they’re probably creating a wrong modeling and mixing data from objects that should be different.

  • Each application/operation must have privileges to access one part, but not another and to give these privileges usually have to separate the tables (database deficiency). It was probably another domain anyway.

  • It can take a long time to add a new column in the table and could block applications from using the table for a long time (DB deficiency), then it is better to create a new table associating. Of course it’s funny.

  • Because it is so for reasons of replication or some specific strategy.

  • The ORM model or other tool that the person uses or for any issue related to the legacy requires something like this.

  • One has no idea what they’re doing.

  • 1

    In the example 1:1, I particularly perceived it as being 1:[0,1]. After all, a Pessoa can simply exist, without yet being bound to be Cliente or Funcionario

  • 1

    @Jeffersonquesado you’re right, I’ll make it better.

  • 1

    Honestly, I just figured it out 1:1 that: Secondary parts of a table/object data; but even in these cases I agree very much with your caveat that is probably a premature optimization (I’m missing about 4 items to finish reading)

  • 2

    Who will determine the rule of the relationship is the domain, I hope that with the edition this is clearer. There is no fixed rule, so one cannot say that it is or is not without knowing the specific case. I have devised some cases that would have rules that make it be 1:1 or 1:(0,1).

  • 1

    The editing was very good

6

What is the advantage of using several tables with 1:1 relationship, instead of using everything in the same table?

I would say the most significant advantage would be:

  • Segmentation by domain
    Assume a case where a table has Customer data, including Phones. These are two different domains, and different use cases can be applied if the table is Partified (two Customers with the same Phone, or one Customer with two Phones.)
  • Domain partitioning 1:[0.. 1] (Zero or One)
    A single table can contain low data density (multiple records with a large amount of fields with NULL or equivalent value). Partitioning in this case would help in queries performance (since the engine would not have to ignore out-of-scope values.)
  • 1

    TelephoneS is already 1:N. If it’s TelefonAnd, I don’t see the point unless I have a lot of related data, even so, with caveats, and I don’t see it happening. If it is separated because several people can have the same phone is N:1. The reason is real, the example is not 1:1

  • @Maniero actually I wanted to express the presence of N Customers and N Phones in the same table, not that the table has, for example, the fields Telefone1 and Telefone2.

  • I believe that the example would be better, as: I have a register of Immovel, and the Immovel, has an Address. (an address can only be a property, and a property belongs to only one address). That is, 1:1.

  • @Ericosouza agree, this is a valid example. Still a customer can own or not a property, or own N real estate. Domain segmentation would allow the expression of these use cases without repetition (or omission).

  • @Onosendai In fact no: (dois Clientes com um mesmo Telefone, ou um Cliente com dois Telefones.), that is 1:N, N:1 or even N:M if you can both. This is the definition of what is not 1:1.

  • @Onosendai, right, but speaking of the domain Immobile, and Imovelendereço. Is it better to divide in 2 tables? Thinking that most of the time I use the data from the immovable table, I do not need the data from the table.

  • @Maniero, that my thinking above is valid?

  • @Maniero Yes, I agree. And this is the point: In the case of domain segmentation, the advantage is that now it is possible to express 1:N relationships without repetition. In the monolithic model it would be necessary to either repeat the customer data + the new phone, or create a more.

  • @Ericosouza Logically speaking a property can only own an address, unless you consider trailers, for example. In the first case the address of a property is part of your domain, and I see no need for partitioning.

  • @Ericosouza yes, but it seems to me that it is only as optimization. For me the address of the property is the same domain of the property. But there may be some rule that says otherwise. I do not see the property exist without the address and the address without the property and it seems to me the same object. I would need to justify why the address should be separated. I can only see because of performance (which I doubt is necessary). But there may be some that I don’t know that conceptually is different. Conceptually for me it is the same thing and should be on the same table.

  • @Onosendai but the question is this, the example does not speak of a case that is 1:1. The example is correct and useful, but it is not 1:1.

  • @Sweet Maniero, now you made me think, and have a vision I didn’t have yet. Really, if I’m going to use a table ONLY for real estate address, it would make no sense to leave separate. Now if the address table is used for more things, it makes more sense.

  • 1

    @That’s what Ericosouza is all about. That’s why the Norel model is usually stuck in most of the problems we solve. Norel preaches gods objects and the real world is not like that. It creates nightmares in many cases, at least in business. It works when you can set rules. You know why Gmail doesn’t have a lot of Feature what other email clients do you have? Because they don’t fit their database model. Yeah, they screw up the UX to get more database efficiency. SE does this with this site at some points.I mentioned this at https://answall.com/a/252792/101 but there were those who liked it

  • @Maniero If I need to store both the address of the property and a user’s home address and a user’s work address, how should I proceed? Create a table address, and create a column addressa_id as a foreign key for the address table.id in the immovable table? And in the table user create two columns also as a foreign key for the address table.id: address_address_id and work_address_id? It would be wrong to do so?

  • That is a different problem than what is answered here.

Show 10 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.