Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

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

  1. Nov 14, 2015 #1

    WWGD

    User Avatar
    Science Advisor
    Gold Member

    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.
     
  2. jcsd
  3. Nov 14, 2015 #2

    PeroK

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    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.
     
  4. Nov 16, 2015 #3

    Svein

    User Avatar
    Science Advisor

    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.
     
  5. Nov 16, 2015 #4

    DaveC426913

    User Avatar
    Gold Member

    How does this example help the OP? A track::album relationship is a one-to-many relationship. Of course they're separate tables.
     
  6. Nov 16, 2015 #5

    Svein

    User Avatar
    Science Advisor

    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.
     
  7. Nov 16, 2015 #6

    PeroK

    User Avatar
    Science Advisor
    Homework Helper
    Gold Member

    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!
     
  8. Nov 21, 2015 #7

    harborsparrow

    User Avatar
    Gold Member

    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.
     
  9. Jan 19, 2016 #8
    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)
     
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook




Similar Discussions: Relational DB: Examples of When 1:1 Should be Kept Seperate versus Together
  1. SHA-1 Broken (Replies: 4)

Loading...