Database Normalization – When Not to Condense One-to-One Relationships

databasenormalizationrelational-database

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)?

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 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.

Related Topic