Database Design Question

Question

Hi,

I am designing a database for a project. I have a table that has 10 columns, most of them are used whenever the table is accessed, and I need to add 3 more columns;

View Count
Thumbs Up (count)
Thumbs Down (Count)

which will be used on %90 of the queries when the table is accessed. So, my question is that whether it is better to break the table up and create new table which will have these 3 columns + Foreign ID, or just make it 13 columns and use no joins?

Since these columns will be used frequently, I guess adding 3 more columns is better, but if I need to create 10 more columns which will be used %90 of the time, should I add them as well, or create a new table and use joins?

I am not sure when to break the table if the columns are used very frequently. Do you have any suggestions?

Thanks in advance,

Answer 1

since it's such a high number of usage cases (90%) and the fields are only numbers (not text) then i would certainly be inclined to just add the fields to the existing table.

edit: only break tables apart if the information is large and/or infrequently accessed. there's no fixed rule, you might just have to run tests if you're unsure as to the benefits.

Answer 2

The frequency of usage should be of no concern for your table layout, at least not until you start with huge tables (in number of rows or columns)

The question to answer is: Is it normalized with the additional columns. Google it, there a plenty of resources about it (with varying quality though)

Answer 3

My advice is the same as cedo's:go with the 13 columns.

Adding another table to the DB, with another Index might just eat up the space you saved but will result in slower and more complicated queries.

Answer 4

Space is not a big deal these days - I'd say that the decision to add columns to a table should be based on "are the columns directly related to the table", not "how often will the columns be used".

So basically, yes, add them to the table. For further considerations on mainstream database design, see 3NF.

Answer 5

Try looking into Database Normalization for some clearly outlined guidelines for planning database structures.

Answer 6

Ditto some earlier posters. 95% of the time, you should design your tables based on logical entities. If you have 13 data elements that all describe the same "thing", than they all belong in one table. Don't break them into multiple tables based on how often you expect them to be used or to be used together. This usually creates more problems than it solves.

If you end up with a table that has some huge number of very large fields, and only a few of them are normally used, and it's causing a performance problem, then you might consider breaking it up. But you should only do that when you see that it really is causing a performance problem. Pre-emptive strikes in this area are almost always a mistake.

In my experience, the only time breaking up a table for performance reasons has shown any value is when there are some rarely-used, very large text fields. Like when there's a "Miscellaneous Extra Comments" field or "Text of the novel this customer is writing".