0

I'm designing a database from scratch and I'm wondering if the way I'm using one-to-one relationships is correct.

Imagine I have a table that needs the columns city and country_id, the first being alphanumeric and the second being a foreign key to another table. Should I place these in a locations table and use a one-to-one relationship?

Another example:

I have a table with the factory information of a device like the serial number and other fields. These will later be used to register a device in another table. Of course this is a one-to-one relationship, but should the columns of the first table be in the second table instead? Have in mind that the registrations table has another 4-5 columns.

I've read a lot of times that these relationships can often be omitted. However, I like the separation of concerns that creating a new table can give, in some cases.

Thanks in advance!

Micael Mendes
  • 1

1 Answers1

0

This may be a duplicate question, e.g. see: SQL one to one relationship vs. single table

There's no perfect answer to this question as it depends on use case, but here's the rule of thumb I recommend abiding by: if you can already envision the potential need for separate tables, then I would err on the side of splitting them and using a 1:1 relationship. For example, imagine in the future that you want to have some kind of one-to-many relationship between some new table and the country table, or between some new table and the device table. In these cases you probably don't want city information mixed up with the former, and you probably don't want device registration information mixed up with the latter. By keeping your DB schema normalized, you can better future-proof it and you can mitigate the need for (likely extremely painful) updates that may have otherwise cropped up.

nb1987
  • 1,400
  • 1
  • 11
  • 12