最好有数百列或分割成多个表？ [英] Best to have hundreds of columns or split into multiple tables?

问题描述

我正在设计一个关于机械设备运行统计数据库。每批数据将包含数百个统计信息，所以我试图决定是否创建一个具有数百列的单个表，或将其分成多个表，每个表包含相关的统计信息。例如，我可以有一个表包含与故障相关的统计信息，另一个表与具有堵塞的统计信息等。

使用多个表将使系统更复杂总的来说，虽然在概念上，我可以比一个大的一个处理几个较小的表可能更容易。

分裂事物会有什么性能优势吗？看起来像用几十列查询表可能会比查询数百列更快。

有没有人有这样的经验？我正在使用Oracle进行这个项目，虽然这是将来我可能会遇到的数据库，但任何数据库的答案都将不胜感激。

解决方案

我想我们需要更多地了解您的设计来正确回答。例如，我很好奇，可能会有很多与故障有关的专栏，有很多（不同的）与果酱等有关的问题（不是堵塞只是一种故障？）

您的设计是否正规化？大概你没有jam1，jam2等列？？？？？？？？？

假设设计是好的和正常化的，决定是否有一个广泛的表格或许多较窄的表格是各种因素之间的折衷：

• 所有/大多数记录是否具有所有类型的统计信息？是=>一个表，否=>多个


• 您是否经常需要一起查询所有类型的统计信息？是=>一个表，否=>多个


• 您是否在同一个屏幕中维护所有不同的统计信息？是=>一个表，否=>多个


• 您是否可能遇到任何数据库限制，例如每桌最多1000列？

无论以哪种方式，您都可以使用视图来呈现替代结构，以方便开发人员：

• 一个表：许多视图选择特定类型的统计信息


• 许多表：将所有表连接在一起的视图

更新

从你的意见中，我现在知道你有机器上40个不同位置的卡纸，其他类型的统计数据是类似的数据。这表示下表设计：

 创建表机（machine_id ...主键，...）; 
创建表machine_stats 
（machine_id参考机器
，stat_group  - 'jams'，'malfunctions'等
，stat_name  - '在引擎盖下'，'在门后'
，stat_count 
）; 
  

正如有人在下面评论的，这些允许您更容易地统计统计数据 - 在统计类型内或跨类型。如果需要将新的统计信息添加到统计类型中，也可以轻松扩展。

I'm designing a database of statistics about the operation of mechanical equipment. Each batch of data will contain hundreds of statistics, so I'm trying to decide whether to create a single table with hundreds of columns or to split it up into multiple tables, each containing related statistics. For example, I could have one table containing the statistics related to malfunctions, another table with the statistics related to jams, etc.

Using multiple tables would make the system more complex overall, though conceptually it might be easier for me to deal with several smaller tables than one big one.

Would there be any performance benefits to splitting things up? It seems like querying a table with a few dozen columns would probably be faster than querying one with hundreds of columns.

Does anyone have any experience with this sort of thing? I'm using Oracle for this project, although this is something I'll likely run into with out databases in the future, so answers for any database would be appreciated.

解决方案

I think we need to know more about your design to answer properly. For example, I'm curious that there could be lots of columns relating to malfunctions, lots (of different ones) relating to jams etc. (Isn't a jam just a kind of malfunction anyway?)

Is your design normalised? Presumably you don't have columns like "jam1", "jam2", etc.?!

Assuming the design is good and normalised, the decision as to whether to have one wide table or many narrower ones is a trade-off between various factors:

• Do all/most records have statistics of all types? Yes => one table, no => many
• Do you often need to query statistics of all types together? Yes => one table, no => many
• Do you maintain all the different stats together in the same screen? Yes => one table, no => many
• Are you likely to hit any database limits e.g. max 1000 columns per table?

Whichever way you go, you can use views to present the alternative structure for the convenience of the developer:

• One table: many views that select stats of particular types
• Many tables: a view that joins all the tables together

Update

From your comments, I now know that you have counts of jams at 40 different locations on the machine, and other types of stats are counts of a similar nature. This suggests the following table design:

create table machines (machine_id ... primary key, ...);
create table machine_stats 
   ( machine_id references machines
   , stat_group -- 'jams', 'malfunctions' etc.
   , stat_name  -- 'under the hood', 'behind the door' etc.
   , stat_count 
   );

As someone commented below, these allows you to sum stats more easily - within or across stat types. It is also easily extended if a new stat needs to be added to a stat type.

这篇关于最好有数百列或分割成多个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！