Is it better to have large tables or many tables (MySQL)

Question

In terms of performance, is it advisable to have only a few tables with a lot of rows or a lot of tables with only a few rows? In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to? Which version would be better in terms of scalability? I am using PHP to access the data and I only need to get data for one or two groups per page load. I am mainly concerned with the speed of the system when there is a lot of data being stored. Thanks.

Answer 1

You should ask youself whether those seperate groups are going to need the same table structure. In a relational database, seperate tables should represent seperate entities. Having your database structured this way keeps entities with like fields structured together and creates a logical flow in a database.

As much as possible, try to structure your database so that seperate entities have their own tables. Even if this were to somehow cost some neglegable extra performance, it would save you hours upon hours when writing queries or trying to expand the database.

Trust me, as someone who has worked with databases where tables contain information that logically should exist as five or six tables, you will be saving yourself a lot of headache..

Answer 2

It is all about data, if you have similar data in multiple groups, there is no logic in storing it in multiple table. Always better to store same type of data in a table (entity).

For example, when a group having an attribute Mobile_Number, then it there is no logic in storing Mobile_number column in multiple tables. You should have simply a table with Mobile_number and have relationship with the groups which have Mobile_number attribute.

By doing this way, you can manage the data in much better way, for example, some groups may have a common attribute such as group_name, other groups may not have group_name but they may have group_number, etc...in this case you cannot insert NULL column in table if you store in a single table.

Here comes, the concept of normalization and denormalization, as per your requirement you will store the data in table. Also, you should consider the future requirements as well, in real world there will be unexpected business requirements, for example, in future your business may require a special attribute such Group_code only to specific groups, if you store it in a single table, you process of altering table every requirement is huge task.

I advice you to understand and gather the business requirements clearly and start with the conceptual data model to physical design, in the long run you will understand its benefits.

I hope this answer will give you an idea. Thanks!.

Answer 3

In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to?

Define "very, very large".
Millions of rows? That's large[-ish]. Anything less than that really isn't.

If you went with multiple tables, how would you identify them?
You don't want to use Group Names as Table Names, because someone will create a Group containing a character that can't be used as such and break everything. So you'd wind up with another table that links each table name to its Group name using some other, arbitrary identifier.

Assuming you did this; how would you then write any sort of query that goes across all of those tables, which you might need to do for Administrative purposes. Let's assume that the database is powerful enough to be able to join or union that many tables in one query? (MySQL, for example, used to be limited to 61 such tables).

Basically, the table-per-thing model is generally a Bad Idea and should be avoided.

Create a single Groups table, with a unique identifier for each Group and index it sensibly for the queries that you intend to run against it.

Answer 4

Having one table with a group column sound more sensible to me for a number of reasons. First, you can take advantage of SQL's GROUP BY functionality to get descriptive stats of each of the groups. Better yet, add an index to your table and it will also run fast. Second, if you need to add more groups, you just add more data to the same table. On the other hand, if you have separate tables for each group you would have to add more tables. And you would need to worry about potentially joining together the group tables each time you query; this is not a nice option, and might not scale well.