[RESOLVED] One table or multiple tables?

luv2climb

Hello,

Does anyone thinks it's better to use one table for a lot of information, or use multiple tables to separate the info.

For example, I am going to be having different areas from all of the western states reporting recruiting activity each week. If there are five regions reporting in each week, and about 10 people in each region reporting, would you recommend just having all the info in one table seprated by a regional name, or have a separate table for each region?

My main question is: Does MySQL start to reach any sort of limit in the number of rows in a table that it can effectively process? If there were, say, 4,000 rows in a table, does that become a problem?

Lame question, I think. But thank you for your input, anyway.

dougal85

Large numbers of rows are not really a problem if the database is set up properly, with index's.

As how how many tables it depends on your data. but i guess you would be looking for a table holding regions a talbe holding the people in the regions... not sure what else. It's had to imagine the data you have from the small explication.

rincewind456

Large numbers of rows in any database are counted in the multi-millions not in the thousands.

MarkR

Always have one table rather than several.

In general, you shouldn't store the same type of data in two or more different tables. Each table should be for one class of item, not for different groups of the same items (e.g. different countries or regions' data should be in a single table, with the country / region ID foreign keyed if that is mandated).

4000 rows is so small that it's hardly worth indexing (but still good practice).

Mark

luv2climb

Thank you. That answers that concern. Now, is it possible to "Select * from table WHERE region = x AND month = y AND year = z"?

In other words, can the "where" clause be used more than once in the same query to filter out specific data? And if so, what is the proper query structure? I tried the MySQL manual but could find nothing.

Thank you for all of your help.

Piranha

The easiest way to know if something works or not is usually to test it out. Just be sure to test on the development server and not the production server.

Your query would probably work if x, y and z are numbers. And the query structure is good.

bradgrafelman

I would say don't SELECT unless you're going to be using data from every single column in that table. Even then, you shouldn't use SELECT for scalability reasons.

Other than that - and the fact that x/y/z must be numbers as Piranha pointed out - your query syntax is fine.

MarkR

You can certainly use WHERE month=x and YEAR=y and region=z

I wouldn't worry about using "SELECT *" too much, unless you have some big text or blob columns; it's essentially an optimisation which only improves performance marginally in most cases.

As a general way to avoid the performance hit of "SELECT " but still keep maintainability, consider creating a view with the commonly-used columns from that table in, and just SELECT from that.

Mark

luv2climb

Thank you all for your assistance and time. You have been very helpful.