Relational DB: Examples of When 1:1 Should be Kept Seperate versus Together

[WWGD]

Hi All,
The "best practices" in RDB on 1:1 relations between entities are that the two entities E1, E2 should be merged into a single one by default, i.e., unless there is an overwhelming reason to keep them separated.
Can someone provide illustrating examples of when the two entities should be merged and some of when the two should be kept separated? I think this has to see with deciding , in a given context , whether one entity can be more reasonably (within the context) when E1 can be seen as an attribute of E2 . Another reason I can think of is that we may want to keep them separate in order to restrict access to information/attributes in a given table, like, say SSN #.
Thanks.

 

[PeroK]

Hi All,
The "best practices" in RDB on 1:1 relations between entities are that the two entities E1, E2 should be merged into a single one by default, i.e., unless there is an overwhelming reason to keep them separated.
Can someone provide illustrating examples of when the two entities should be merged and some of when the two should be kept separated? I think this has to see with deciding , in a given context , whether one entity can be more reasonably (within the context) when E1 can be seen as an attribute of E2 . Another reason I can think of is that we may want to keep them separate in order to restrict access to information/attributes in a given table, like, say SSN #.
Thanks.

One consideration is whether the 1:1 relationship is unchangeable. Perhaps "shipping address" is a good example. Even if the initial data specification is for one address, it may be wise to allow for more, just in case.

 

[Svein]

I can give you an example: I have created a database of my music collection. The tables are:

• "Album", containing the title, the media type and the physical placement (where is it?)
• "Track", containing the track no., the title, the performer and the composer

There would be no sense at all duplicating the "Album" info in each track record.

Of course, there are more tables and more fields, but I have shown you the basics.

 

[DaveC426913]

I can give you an example: I have created a database of my music collection. The tables are:

• "Album", containing the title, the media type and the physical placement (where is it?)
• "Track", containing the track no., the title, the performer and the composer

There would be no sense at all duplicating the "Album" info in each track record.

Of course, there are more tables and more fields, but I have shown you the basics.

How does this example help the OP? A track::album relationship is a one-to-many relationship. Of course they're separate tables.

 

[Svein]

How does this example help the OP? A track::album relationship is a one-to-many relationship. Of course they're separate tables.

Sorry, I did not see that qualification. I do not have a ready example for a one-to-one relationship, but I can think of one based on the union concept in C. Imagine a Customer table which is either linked to a National or an International shipping table. Those two shipping tables will contain different information, but only one set of Shipping information is connected to each customer.

 

[PeroK]

Potentially another reason is the technical characteristics of the data. Digital images might be separated from a regular table. And DICOM images, representing CT and MRI scans, or other medical data in the form of a large digital file would be separated using specialist technology.

In fact, all radiology systems are split into a Radiology Information System and a PACS (Picture Archive and Communication System), with the RIS providing the regular functionality (schedluing and patient information etc.) and the PACS specialising in the handling of large digital images. That would be the ultimate in terms of the characteristics of the data driving the design of the system into two separate but integrable parts. Way beyond just database design, in any case!

 

[harborsparrow]

A classic reason to separate 1:1 data would be security. So it might be okay for some people to see names but not social security numbers. Putting the Socials in another table means you can set completely different access control levels for the sensitive data.

There might also be efficiency reasons. I'd have to think about that. Like maybe, very very rarely accessed data.

One reason I have done it in the past was that I inherited a god-awful, crappy old data model, where a lot of stored procedures, triggers, enforced relationships and existing code get mad if I add any fields at all to my existing table. So my god-awful, crappy workaround has been to create a parallel extension table with 1:1 relationship and just join them whenever I need them.

 

[Howard Bell]

There are many reasons for seperating 1:1 relational data here are a few:

1. If there is broad separation of the data's functional use: Example entity 'Person' with attributes relating to Medical Condition and Attributes relating to Salary, these attributes will probably be accessed in different functions or under different security regimes.
2. Access speed, if an attribute of an Entity is a Binary Large Object (BLOB/LOB/CLOB) then seperating it from non-large attributes of the same entity makes sense from a performance point of view (especially if the BLOB is infrequently needed but the other attributes are accessed often.
3. Semi related to point 1, if you need the ability to independantly and concurrently update two attributes of the same entity occurance, split them. Databases typically lock at row level, so Bob might lock (for update) a row of people_medical while sally locks the equivalent row of people_salary. This can be very important in big systems to avoid lock failures.
4. Separation of historical values for performance, ie People.salary People.last_years_salary (not a great example, but attributes that are unlikely to be needed regfularly)