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)?
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
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.
You may want different backup strategies and therefore put two tables on different partitions, e.g.
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.
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.
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
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;
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.