数据库(MySQL)结构:多个表的优缺点 [英] Database (MySQL) structuring: pros and cons of multiple tables

问题描述

我正在收集数据并将其存储在MySQL中，用于:

I am collecting data and storing it MySQL, for:

• 75个变量
• 55个国家
• 每年

自从我构建此工具以来，在这个阶段我已经创建了一个变量/国家/地区表(存储1年的数据).

I have, at this stage since I am building this tool created a single table, of variables / countries (storing 1 year worth of data).

明年(及其后的几年)，将为每个国家/地区输入一组新数据.

Next year (and for several years after that) a new set of data will be input for each country.

因此，在控制返回给查看所有收集数据的用户的数据方面，存在3个变量.任何查询的一般形式为:

There are therefore 3 variables in controlling data returned to a user reviewing all collected data. The general form of any query would be:

• 向我显示这些特定变量，用于这些特定国家/地区，以及这些特定年份.

• Show me these specifics variables, for these specific countries, for these specific years.

(例如，向我显示2012年和2009年美国和加拿大的平均年龄和体重)

(Show me average age and weight, for USA and Canada, for 2012 and 2009, for example)

我的问题是，似乎我有两个选择来安排此数据:-多个表，每年收集一个我创建国家/变量的表-单个表格，只需添加与数据相关的年份的列(字段)即可.

My question is, it seems that I have two options for arranging this data: -Multiple tables where I create a table of country / variable for each year data is collected - Single table and simply add a column (field) for the year that data relates to.

据我所知，我可以使用任何一种结构来进行这些数据库调用，但是这样做更强大/有效/更快，为什么?

As far as I can tell I could make these database calls with either sructure, but is one more powerful / efficient / quicker, and why?

感谢您的考虑.

如果是相关事件，则为PDO/PHP接口.

It's a PDO / PHP interface if that is relevent.

推荐答案

使用关系方法通常会涉及更多表.这将导致查询速度变慢(尽管在小型数据库中可能不明显)，并且数据库大小变小.这样可以更轻松地正确更新信息，从而确保数据完整性.例如，如果您更改了乔的地址，那么您将知道所有报告都将使用乔的地址进行更改.

Using a relational approach generally involves more tables. This translates into queries being a bit more slow (though probably not noticeable in small databases) and database size to be smaller. This makes it simpler to update information properly and thus ensure data integrity. For example, if Joe's address changes you know it will be changed on all reports using Joe's address.

使用链接较少的表(一个字段可以重复多次)，您可能会冒着不同表的数据之间的差异的风险，而您自然希望它们相等.如果正确安排表格，访问速度应该会更快一些，因为信息将根据访问方式进行分组.

Using less linked tables where one field can be repeated multiple times you risk having disparity between data from different tables where you would naturally expect it to be equal. Access speed should be a bit faster if you arrange your tables properly because your information will be grouped according to how you access it.

例如，在第一种方法中，您将具有一个 Orders 表以及一个 Supplier 和 Client 表，以制作完整的发票，而在第二种方法是，您希望将 Supplier 和 Client 的一些信息放在 Orders 表中，以便访问该行以找到与您要查找的发票号将返回所需的整个数据集(从而消除了在 Supplier 和 Client 上进行联接的需要，并减少了数据库服务器上的负载).

For example, in the first method you would have an Orders table with a Supplier and Client table to make a complete invoice whereas in the second method you would want to put some information of both Supplier and Client in the Orders table such that accessing that finding the row corresponding to the invoice number you are looking for would return the entire set of data that you need (thus eliminating the need for joins on Supplier and Client and reducing load on the database server).

我认为一个更好的答案将需要更多有关您数据的信息(例如样本).

I think a better answer would require a bit more information about your data (samples for example).

这篇关于数据库(MySQL)结构:多个表的优缺点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！