我的软件几天前投入生产，现在我想讨论一下数据库结构。
该软件收集有关船舶的数据，目前每艘船舶有174个详细信息，每个详细信息可以是文本值、长文本值、数字（指定长度，带或不带指定小数位数）、日期、日期时间、布尔字段、包含多个值的菜单、数据列表等等。
我用下表解决了问题
Ship:
- ID - smallint, Autoincrement identity
- IMO - int, A number that does not change for the life of the ship
ShipDetailType:
- ID - smallint, Autoincrement identity
- Description - nvarchar(200), The description of the value the field contains
- Position - smallint, The position of the field in the data input form
- ShipDetailGroup_ID - smallint, A key to the group the field belongs to in the data input form
- Type - varchar(4), The type of the field as mentioned above
ShipDetailGroup
- ID - smallint, Autoincrement identity
(snip...)
ShipMenuPresetValue
- ID - smallint, Autoincrement identity
- ShipDetailType_ID - smallint, A key to the detail the values belongs to
- Value - nvarchar(100), The values preset in the menu type detail
ShipTextDetail
- ID - smallint, Autoincrement identity
- Ship_ID - smallint, A Key to the ship the detail belongs to
- ShipDetailType_ID - smallint, a Key to the detail type of the value
- Text - nvarchar(500), the field containing the detail's value
- ModifiedDate - smalldatetime
- User_ID - smallint, A key to the user table
ShipTextDetailHistory
(snip...)
This table is the same as the ShipTextDetail and contains every change to the details.
Other tables for the list detail type, each with the specified fields required for the list, ...
，我刚刚读了这篇文章： http://thedailywtf.com/ Articles/The_Inner-Platform_Effect.aspx 和 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056
文章说这不是正确的方法处理问题。
我的客户有一个用于详细信息和组的管理 GUI，因为他更改了详细信息描述并添加了更多详细信息。
数据输入表单是通过从 DetailGroups 和 DetailTypes 读取结构动态构建的，每个详细信息类型生成一个指定的输入控件。
评论表明解决此问题的另一种方法是动态创建和从表中删除列。
你怎么认为？
图表截图： http://img24.imageshack.us/my.php?image =66604496uk3.png
My software went in production some days ago and now I want to argue a bit about the database structure.
The software collects data about ships, currently 174 details for each ship, each detail can be a text value, a long text value, a number (of a specified length, with or without a specified number of decimals), a date, a date with time, a boolean field, a menu with many values, a list of data and more.
I solved the problem with the following tables
Ship:
- ID - smallint, Autoincrement identity
- IMO - int, A number that does not change for the life of the ship
ShipDetailType:
- ID - smallint, Autoincrement identity
- Description - nvarchar(200), The description of the value the field contains
- Position - smallint, The position of the field in the data input form
- ShipDetailGroup_ID - smallint, A key to the group the field belongs to in the data input form
- Type - varchar(4), The type of the field as mentioned above
ShipDetailGroup
- ID - smallint, Autoincrement identity
(snip...)
ShipMenuPresetValue
- ID - smallint, Autoincrement identity
- ShipDetailType_ID - smallint, A key to the detail the values belongs to
- Value - nvarchar(100), The values preset in the menu type detail
ShipTextDetail
- ID - smallint, Autoincrement identity
- Ship_ID - smallint, A Key to the ship the detail belongs to
- ShipDetailType_ID - smallint, a Key to the detail type of the value
- Text - nvarchar(500), the field containing the detail's value
- ModifiedDate - smalldatetime
- User_ID - smallint, A key to the user table
ShipTextDetailHistory
(snip...)
This table is the same as the ShipTextDetail and contains every change to the details.
Other tables for the list detail type, each with the specified fields required for the list, ...
I just read this article: http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10678084117056
The articles says that this is not the right way to handle the problem.
My customer has a management gui for the details and groups as he changes the details descriptions and adds more details.
The data input form is dynamically built by reading the structure from the DetailGroups and DetailTypes, each detail type generates a specified input control.
The comments suggests that another way of solving this matter is dynamically creating and removing columns from the table.
What do you think?
Diagram Screenshot: http://img24.imageshack.us/my.php?image=66604496uk3.png
发布评论
评论（4）
如果出现以下情况，我会重构您的代码：
你知道将会发生的改变
将来。
您记得编写允许重构的单元测试，对吗？
*就你那里的结构而言，我以前见过类似的结构。 虽然有点麻烦，但在很多地方都是标准的。 要记住的一件事是，虽然可以从数据库中动态添加和删除列，但数据库的内部存储机制并不一定要求您连续添加和删除这些列。 但我认为这与上面的几点相比不是很相关，上面的几点可以归结为：*它有效吗？
I would refactor your code if:
change you knew was going to happen
in the future.
You remembered to write unit tests that will allow you to refactor, right?
*As far as the structure you have there, I've seen structures like it before. It's a little cumbersome but it is standard in many places. One thing to remember is that while its possible to dynamically add and remove columns from databases, the internal storage mechanism of the database doesn't necessarily expect you to be adding and removing these columns continuously. But I don't think this is very relevant compared to the points above, which boil down to: *Does it work?
我以前见过这种方法，一旦数据量增加，它就会带来大量的性能问题。 当您需要返回多个项目并在 where 子句中使用多个条件时，您就会遇到这种问题。 您在 Ship 和 ShipTextDetail 之间来回连接以获取所有选择的列 - 也许您必须这样做 10/20 次？ 然后，您可能会根据您的标准执行相同的操作 2-3 次。 现在你有一个包含如此多连接的查询，它运行得非常慢。 接下来，您“预煮”一些数据以提高性能，即将通用数据拖出到固定的表结构中 - 啊，您已经返回到半标准化模型。
我的建议是这样的 - 你知道 174 个字段的信息，这些是你的核心属性。 您的客户可能会添加到该列表中，并且可能会更改字段的描述，但这仍然是一个非常好的起点。 基于这些创建适当的数据模型，然后构建可扩展性机制，就像您已经完成的那样，但仅限于新字段。 元数据 - 字段的描述，可以驻留在另一个表中，或者可能驻留在资源文件中（对于国际化有用？），这为现有字段提供了一定的灵活性。
我同意 Joe 的观点，如果您的数据库很小，即 <1000 艘船，并且您的选择很简单，那么您可能不会遇到问题。 尽管有 174 个属性可供选择，但这似乎不太可能。 我认为您应该首先更改一些“明显”字段，即我假设您有 Ship.Name、Ship.Owner、Ship.Weight、Ship.Registration...
祝您好运。
I've seen this approach before and it's presented loads of performance issues once the data volume has grown. The kind of problems you'll encounter come when you need to return multiple items and use multiple criteria in your where clause. You join back and forth between Ship and ShipTextDetail to get all your select columns - maybe you have to do that 10/20 times ? You then do the same for your criteria maybe 2-3 times. Now you have a query with so many joins it runs really slowly. Next you 'pre-cook' some of the data to improve performance, ie you drag out common data into a fixed table structure - ah you've returned to a semi-normalised model.
My recommendation would be this - you know the information for 174 fields those are your core attributes. Your customer may add to that list, and may change the description of the fields, but it's still a really good starting point. Create a proper DataModel based around those, and then build in an extensability mechanism, as you have already done, but only for the new fields. The metadata - the descriptions of the fields, can reside in another table, or potentially in a resource file (useful for internationalisation?) and that gives some flexibility for existing fields.
I agree with Joe, you may not have problems if your DB is small, ie <1000 ships and your selects are simple. Although with 174 attributes to chose from this doesn't appear likely. I think you should change some of the 'obvious' fields first, ie I'd assume you have a Ship.Name, Ship.Owner, Ship.Weight, Ship.Registration ...
Good Luck.
我已经做过类似的事情，但是这个特定的实现有几个问题：
I've done similar things, but there are a couple problems with this specific implementation:
从性能的角度来看，这两种方法都可以。 可能有多少艘船？ 所有数据都将适合任何服务器上的 RAM。
From a performance standpoint, either approach will be fine. How many ships could there possibly be? All the data is going to fit into RAM on any server.