CODEWITHSUNDEEP
mysqldatabasedatabase-designjoinone-to-one
RS7
RS7

Reputation: 2361

MySQL - When to have one to one relationships

When should one use one to one relationships? When should you add new fields and when should you separate them into a new table?

It seems to me that you'd use it whenever you're grouping fields and/or that group tends to be optional. Yes?

I'm trying to create the tables for an object but grouping/separating everything would require me about 20 joins and some even 4 levels deep.

Am I doing something wrong? How can I improve?

Upvotes: 0

Views: 439

Answers (1)

Nathaniel Ford
Nathaniel Ford

Reputation: 21269

First, I highly recommend reading about Normal Forms

A normalized relational database is extremely useful, and doing this properly is the reason tools such as Hibernate exist - to help manage the difference between objects-represented-as-relational-mappings and objects-as-progrommatic-entities.

Anything that has a one-to-one mapping should probably be in the same table. A Person has only one first name, one last name. Those should logically be in the same table. Having a reference to a table of names isn't necessary - in particular because little additional data can be stored about a name. Obviously, this isn't always true (an etymology database might want to do exactly that), but for most uses, you don't care about where a name comes from - indeed all you want is the name.

Therefore, think of the objects being represented. A person has some singular data points, and some one-to-many relationships (addresses they have lived, for instance). One to many and many to many will almost always require a separate table (or two, to have many to many). Following those two guidelines, you can get a normalized database pretty fast.

Note that optional fields should be avoided if at all possible. Usually this is a case of having a separate table holding the field with a reference back to the original table. Try to keep your tables lean. If a field isn't likely to have something, it probably should be a row in it's own table. Many such properties suggests a 'Property' table that can hold arbitrary optional properties of a particular type (ie, as are applied to a 'Person').

Upvotes: 6

Related Questions