12

If we have Table A that has a one to one relationship with Table B, does it ever make sense to keep them apart? Or does it never hurt to combine them into a single table? Do either of these scenarios (two tables vs one combined table) impact anything with respect to its normal form (1NF, 2NF, 3NF, etc)?

  • 5
    Do you mean 1-to-1 or 1-to-0-or-1? – jpmc26 Aug 20 '16 at 0:04
30

Yes, there are tons of reasons why this may be the better design.

You may have an inheritence/extension relationship, e.g. you might have a User table and then an Administrator table which has more fields. Both tables may have a primary key of User ID (and therefore have a 1:1 relationship) but not all users will have a record in the Administrator table. You would need something similar if you are supporting a workflow, e.g. a ScheduledTask table and CompletedTask table.

You may want to have a lightweight table for commonly-used data User and then a larger table for details you don't need very often UserDetails. This can improve performance because you'll be able to fit more records into a single data page.

You may want different permissions to the tables, e.g. User and UserCredentials

You may want different backup strategies and therefore put two tables on different partitions, e.g. Transaction and TransactionArchive

You may need more columns than can be supported in a single table, e.g. if there are a lot of large text columns that you need to be able to index and your DB platform is limited to 4K data pages or whathaveyou.

| improve this answer | |
  • If your table has more columns than can be supported by the database system, you have a design problem. But otherwise, your answer is sound. – Robert Harvey Aug 19 '16 at 21:08
  • 2
    Agreed... I'm trying for an exhaustive list, not editorializing on each reason. – John Wu Aug 19 '16 at 21:09
  • What is your background in workflows? Do you have specific software you use? Have you rolled your own workflow system? – Robert Harvey Aug 19 '16 at 21:21
  • 1
    Physical = having to do with physical constraints such as server performance, page size, indexing, clustering, backups, etc. none of which should affect the logical design. From a purely logical perspective, a single entity should be conceptualized as a single tuple or row within a single table. – John Wu Aug 19 '16 at 23:37
  • 4
    Link "A one-to-one relationship in a relational database occurs when one parent record or field has either zero or one child record only." – John Wu Aug 20 '16 at 0:06
6

Adding to the excelent answer by @john-wu another, another reason is when you have A BLOB type of column like a picture.

You want to have that BLOB column in a separate table, not only for queries on the user table be quicker but also because you could move the table containing the blob to a different tablespace on cheaper, slower storage, keeping the most queried data in the main tablespace on a faster storage.

| improve this answer | |
3

One to one relationships only really make sense when you want the related record in Table B to be optional.

Sometimes what you want is a variant record or Tagged Union. That means you have multiple tables containing different information, but all relating back to Table A in one-to-one relationships. You then choose which table to associate based on a field in Table A

For example:

type Transaction(The_Type: PaymentType := Cash) is record

    Amount: Integer;

    case The_Type is
        when Cash =>
            Discount: boolean;
        when Check =>
            CheckNumber: Positive;
        when Credit =>
            CardNumber: String(1..5);
            Expiration: String(1..5);
    end case;
end record;
| improve this answer | |
  • If it's optional how is this different from it all being in one table and just selecting the columns you want? – The 29th Saltshaker Aug 19 '16 at 21:03
  • You take the cost of having those additional fields in the main table, even if there's nothing in them. If there are several tables in your variant record, you might have 100 columns in a single table, most of which aren't used most of the time. – Robert Harvey Aug 19 '16 at 21:04
2

In business modeling, two entities A and B which logically are separate from a business point of view typically map to different tables.

For example, when doing business modeling with object oriented means, you typically have some kind of object-relational mapping in place. You might start with an object model and derive your relational model from that. So imagine in your object model you have created classes A and B, which, though the objects have a 1:1 correspondence, should stay separated because of the single responsibility principle. Note in your object model, these classes are not just tables with attributes, they might represent business objects, with some behaviour implemented in methods. And when you map those classes now straightforward to a relational model, you get separate tables A and B with 1:1 relationship.

To my experience, when creating a business or OO data model, this logical separation is far more more typical for 1:1 relationships than "physical" reasons like performance, individual security or partitioning.

| improve this answer | |
  • Can you give a concrete example of what you mean? – The 29th Saltshaker Aug 20 '16 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.