Is this a good case for decomposing a table and having a 1-1 table relationship?

Question

I am storing information about websites in a table. One set of information is the whois data about a websites domain name. This set of data contains about 40 fields and each record relates to a single website. I have no requirement to track updates. I could put all the whois data in the websites table, but it seems 'cleaner' and more intuitive to have the domain whois information in a new table with a 1-1 mapping.

What is the best solution in this case? Is a table with many fields always preferable over two smaller tables with an unnecessary join?

Answer 1

It would probably be easier to leave this as one table and use a view to "simplify" the data for the consumers.

One thing to consider is that your needs may change over time and you'll find you'll need to change how you split the table. If you just use a view, it's very simple to alter a view without having to figure out how to move the data from one table to the other.

Answer 2

A join is always costly. The only reason I would really consider splitting the two is if you will often query one set of columns, and very rarely the other.

Answer 3

It depends on your application. What does your app do with website data? what does it do with the related whois data?

If you often access the website data, and seldom access the whois data (or the other way around) it would make sense to separate them. This is not so much a relational or logical way or reasoning, more a practical, performance-related reason. From a purely relational point of view, it would have to go in the same table.

If i think about it, I am having trouble coming up with a real world genuine 1:1 example that would make sense in a purely relational model. This is not the case for a 1:0 example: subtypes are naturally modeled as a parent table having one or more optional related rows in child tables in a 1:0 fashion.

Answer 4

If the performance hit of the join doesn't bother you, splitting up the data into two tables might make sense (no need to avoid duplicate column names, etc).

If the two sets of data have very different update/read frequencies, splitting can improve cache hit ratio by removing the seldom-used fields into a separate table. But, as all performance things, this is very dependent on your work load, might change on a moment's notice, is not aligned with your relation model and should be throughly benchmarked.

Answer 5

A join doesn't necessarily cost anything. Depending on how the tables are stored the join could be a no-op. Note that such tables are not usually true 1-1 because a foreign key is always optional on one side of the constraint. So if the whois data does not apply to every row then that's a good reason to have two tables.