Hi,

Quick question for potential optimization of my database design.

Is it better to have 1 big table representing a certain type of information or to break it up into a series of related tables?

For example:

I want a table of links from a certain number of websites. Should I have 1 big table like: links or a series of tables, 1 table representing each site?

Opinions and potential pitfalls would be helpful on either side. Thank you.

all the data is the same structure, use one table

In my mind there are very few reasons for splitting the same/similar data into several tables, such as

the number of columns is so big that it blows the limit imposed byt he SQL engine
no one, or vitually no one) consumer of that data is going want or need to see every column, and there are security reasons to push the other stuff into another table. In my view thats a fallacious arguemnt as views can do the same effective trick, or you are using physical design to get round a servers security limitations)
sometimes its becasue the other block/blocks of data are optional.. to me thats just bad physical design, you could always have a sub table with a one to one relationship for that block of optional data

otherwise a separate table for each group is rarely justified, primarily it becomes an admin headache (not just for the DBA but also the developer (you are having to create new tables for each URL, you have to find all those tables).
do comparisons between your data becomes awkward, eg if someonme wants a 'top ten report',
you are loosing the power of the SQL engine to refine, sort and analyse your data

So in short all similar data in the same table, if you start threatening to blow MySQL's limits then you need to rethink your strategy or possibly rethink your server

Hi: healdem, r937

Thanks for the responses. In thinking about the design, I was more worried about the speed of queries/affect on the server that having 1 big table vs. multiple smaller tables would create.

Food for thought. Anyone else like to chime in?

If not, thanks both!

what is one big table? properly indexed, with database properly tuned, mysql can handle in the 10s and 100s of millions of rows without much effort. Do you expect your table to be that big?

Lets say you expect your data to be smaller. Say 100,000 rows in one table or 10,000 in each of 10. When you need data from all 10 of those tables, for stats purposes say, you will need to join or union all 10 tables, significantly slower than searching the one table.

You or others will be hard pressed to come up with good reasons to split the data.

Size is hardly ever a consideration for splitting up tables. Maybe when you've got an enormous amount of data (millions of rows) and 99% of your queries will return rows from the same small subset of the data. For example when you've got a big table with orders you might want to archive orders that have been closed more than two years ago in a history table.

The usual reason to have multiple tables is to avoid redundancy of data. If you want to know about that, look up normalization.