Page 1 of 1

Long row read verses multiple queries (mySQL)

Posted: Mon Dec 20, 2010 11:21 am
by kristen
Which is the lesser of the evils?

- Add more col to a table so that the DB is only getting hit with one query
- Add a second table, hit the DB with 2 queries and leave the rest to code?

Scenario:

I have a table that is already 19 col- the table is a unique ID#, then a pair of values: current value and max potential value. So I chose not to split them into 2 tables because the 2 values must always travel as pairs (everything is in context of potential vs final)

Now I need to add a third set of data (bonuses) So it was would be potential + max + bonuses. This would add another 9 col. Or I could put it in its own table and code the rest.

No actual calculations are done by the database- just very simple selects and the code does all the math.

So. Less evil to add more col, or less evil to have 2 queries?

Re: Long row read verses multiple queries (mySQL)

Posted: Mon Dec 20, 2010 12:13 pm
by califdon
In relational databases, with perhaps an occasional rare exception, there is no such thing as "too many columns" and the answer to "what columns belong in this table" is the same as the answer to "how many tables are needed in this database":

A table represents an entity in your data model. Absolutely everything is determined by that and that alone. The first thing you must always do is clearly define your data model as relationships among entities. Once you have done that, you determine what attributes each entity has, and these are the columns needed in its table. You will then need to consider what Primary and Foreign keys are needed in each table to support the relationships. That's it. There is never (or practically never) a reason to ask yourself questions like "would it be better if I split this column out to a different table." Exceptions to these rules may be necessary in enormously large databases (terabyte files or a table with several hundred columns) for performance purposes or in distributed databases.

Re: Long row read verses multiple queries (mySQL)

Posted: Mon Dec 20, 2010 12:43 pm
by kristen
Thank you. Early on I got hit over the head with the idea that no well designed DB will ever have more then 10 cols per table, and it's always lurking in the back of my mind.

Re: Long row read verses multiple queries (mySQL)

Posted: Mon Dec 20, 2010 7:13 pm
by califdon
kristen wrote:Thank you. Early on I got hit over the head with the idea that no well designed DB will ever have more then 10 cols per table, and it's always lurking in the back of my mind.
Somebody gave you extremely bad advice, then. I can't think of a professional database I ever developed where tables were that small, except for lookup tables.

Re: Long row read verses multiple queries (mySQL)

Posted: Tue Dec 21, 2010 10:44 pm
by Christopher
Good advice califdon!. This is also a case where the issue of Premature Optimization comes into play. And that is probably where the 10 column rule came from. In general you should do what creates the best designed code -- until you have an actual performance problem. Then look into optimizations.

A question here may be: how much duplicated data is in that 19 column data table? And does that duplication matter? If there is a lot of duplication then moving the duplicated data into separate tables and JOINing might be better. Especially if that makes maintaining the data simpler.

But there is also, especially for the web, this newer idea of keeping data closer to the format is it used in, rather than normalizing for the sake of normalizing.

Re: Long row read verses multiple queries (mySQL)

Posted: Tue Dec 21, 2010 10:57 pm
by califdon
Thanks, Christopher. As you probably know, I'm an old-timer and kind of dogmatic when it comes to relational data normalization. I learned it from taking a seminar from Chris Date, who (as I'm sure you know) worked with Ted Codd, the inventor of the relational model and SQL, and who wrote the definitive book on relational databases that is still used in practically every university computer science course across the world. As an old-timer, I may be guilty of distrusting you young whippersnappers and your fancy new ideas, but as a friend of mine says, maybe I'll learn better when I'm younger. :wink: