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)?
Database Normalization – When Not to Condense One-to-One Relationships
databasenormalizationrelational-database
Best Answer
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 anAdministrator
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 theAdministrator
table. You would need something similar if you are supporting a workflow, e.g. aScheduledTask
table andCompletedTask
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 oftenUserDetails
. 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
andUserCredentials
You may want different backup strategies and therefore put two tables on different partitions, e.g.
Transaction
andTransactionArchive
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.