Menu
A DB design question: when do you decide to use 1 to 1 relation tables?
One of the places I see this is, for example, when you have a User and UserProfile table, people split them instead of putting all columns just in a User table.
Technically, you can just put all the columns in one table since their relationship is 1-to-1.
I know someone said that for the UserProfile table, over time you need to alter table to add more columns, but I really don't think this is a strong reason to split the tables.
So, if I'm to design a User table and UserProfile table, is it better for me to just do it in one table?
951
In a relational database, a one-to-one relationship exists when one row in a table may be linked with only one row in another table and vice versa. It is important to note that a one-to-one relationship is not a property of the data, but rather of the relationship itself.
The most common scenario for using a one-to-one relationship in a database is splitting one table into two: one with mandatory columns, the other with optional columns.
One-to-one relationships associate one record in one table with a single record in the other table. One-to-many relationships associate one record in one table with many records in the other table.
A one-to-many relationship is the most common relationship found between tables in a relational database.
The only time I have used a 1 to 1 relationship is when I want it to polymorphically belong to multiple objects.
Like an address for instance. A user has one address, a business has one address, a featured restaurant has one address. All instances are handled in the same table and has the same code that governs it. Think of it like refactoring your datamodel so you can reuse it in other places.
108
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
