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.
该软件收集有关船舶的数据,目前每艘船的174个细节,每个细节可以是文本值,长文本值,数字(指定长度,有或没有指定的小数),日期,日期与时间,布尔字段,具有许多值的菜单,数据列表等。
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
我刚读过这篇文章:http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx和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.
我的客户在更改详细信息描述并添加更多详细信息时,会为细节和组提供管理gui。
The data input form is dynamically built by reading the structure from the DetailGroups and DetailTypes, each detail type generates a specified input control.
通过从DetailGroups和DetailTypes读取结构来动态构建数据输入表单,每个细节类型生成指定的输入控件。
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
图表截图:http://img24.imageshack.us/my.php?image = 66604496uk3.png
4 个解决方案
#1
I would refactor your code if:
如果符合以下情况,我会重构您的代码:
- Your customer complained
- You found something that didn't work
- You found a way that the code couldn't handle a 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?
*就你所拥有的结构而言,我之前看到过类似的结构。这有点麻烦,但它在许多地方都是标准的。要记住的一件事是,虽然可以动态添加和删除数据库中的列,但数据库的内部存储机制并不一定要求您连续添加和删除这些列。但我不认为这与上述要点相比非常重要,归结为:*它有效吗?
#2
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.
我以前见过这种方法,一旦数据量增长,就会出现大量的性能问题。当您需要返回多个项目并在where子句中使用多个条件时,您遇到的问题就会出现。您在Ship和ShipTextDetail之间来回连接以获取所有选择列 - 也许您必须这样做10/20次?然后你可以为你的标准做同样的事情2-3次。现在你有一个有这么多连接的查询,它运行得非常慢。接下来,您“预先烹饪”一些数据以提高性能,即将常用数据拖出到固定的表结构中 - 啊,您已经返回到半规范化模型。
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.
我的建议是这样的 - 你知道174个字段的信息,这些是你的核心属性。您的客户可能会添加到该列表,并可能会更改字段的描述,但它仍然是一个非常好的起点。根据这些创建一个合适的DataModel,然后像您已经完成的那样构建可扩展性机制,但仅限于新字段。元数据 - 字段的描述,可以驻留在另一个表中,或者可能存在于资源文件中(对国际化有用吗?),并为现有字段提供一些灵活性。
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 ...
我同意Joe的意见,如果你的数据库很小,你可能没有问题,即<1000艘船并且你的选择很简单。虽然有174个属性可供选择,但这似乎不太可能。我认为你应该先改变一些“明显的”字段,即我假设你有一个Ship.Name,Ship.Owner,Ship.Weight,Ship.Registration ......
Good Luck.
#3
I've done similar things, but there are a couple problems with this specific implementation:
我做过类似的事情,但这个具体的实现有几个问题:
- You are storing numbers, booleans, dates, etc. as strings. This might be less than ideal. An alternative is to implement separate classes (inheriting from a base) for the different data types then store them in tables made for their data type.
- Do the properties that you track change very frequently? Are they a different set per tanker? If not, it might be better to make objects rather than property bags to store all the data. Those objects can then be persisted to the database.
您将数字,布尔值,日期等存储为字符串。这可能不太理想。另一种方法是为不同的数据类型实现单独的类(从基础继承),然后将它们存储在为其数据类型制作的表中。
您跟踪的属性是否经常变化?它们是每艘油轮不同的一套吗?如果没有,最好是制作对象而不是属性包来存储所有数据。然后可以将这些对象持久保存到数据库中。
#4
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.
从绩效的角度来看,任何一种方法都可以。可能有多少艘船?所有数据都适合任何服务器上的RAM。
#1
I would refactor your code if:
如果符合以下情况,我会重构您的代码:
- Your customer complained
- You found something that didn't work
- You found a way that the code couldn't handle a 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?
*就你所拥有的结构而言,我之前看到过类似的结构。这有点麻烦,但它在许多地方都是标准的。要记住的一件事是,虽然可以动态添加和删除数据库中的列,但数据库的内部存储机制并不一定要求您连续添加和删除这些列。但我不认为这与上述要点相比非常重要,归结为:*它有效吗?
#2
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.
我以前见过这种方法,一旦数据量增长,就会出现大量的性能问题。当您需要返回多个项目并在where子句中使用多个条件时,您遇到的问题就会出现。您在Ship和ShipTextDetail之间来回连接以获取所有选择列 - 也许您必须这样做10/20次?然后你可以为你的标准做同样的事情2-3次。现在你有一个有这么多连接的查询,它运行得非常慢。接下来,您“预先烹饪”一些数据以提高性能,即将常用数据拖出到固定的表结构中 - 啊,您已经返回到半规范化模型。
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.
我的建议是这样的 - 你知道174个字段的信息,这些是你的核心属性。您的客户可能会添加到该列表,并可能会更改字段的描述,但它仍然是一个非常好的起点。根据这些创建一个合适的DataModel,然后像您已经完成的那样构建可扩展性机制,但仅限于新字段。元数据 - 字段的描述,可以驻留在另一个表中,或者可能存在于资源文件中(对国际化有用吗?),并为现有字段提供一些灵活性。
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 ...
我同意Joe的意见,如果你的数据库很小,你可能没有问题,即<1000艘船并且你的选择很简单。虽然有174个属性可供选择,但这似乎不太可能。我认为你应该先改变一些“明显的”字段,即我假设你有一个Ship.Name,Ship.Owner,Ship.Weight,Ship.Registration ......
Good Luck.
#3
I've done similar things, but there are a couple problems with this specific implementation:
我做过类似的事情,但这个具体的实现有几个问题:
- You are storing numbers, booleans, dates, etc. as strings. This might be less than ideal. An alternative is to implement separate classes (inheriting from a base) for the different data types then store them in tables made for their data type.
- Do the properties that you track change very frequently? Are they a different set per tanker? If not, it might be better to make objects rather than property bags to store all the data. Those objects can then be persisted to the database.
您将数字,布尔值,日期等存储为字符串。这可能不太理想。另一种方法是为不同的数据类型实现单独的类(从基础继承),然后将它们存储在为其数据类型制作的表中。
您跟踪的属性是否经常变化?它们是每艘油轮不同的一套吗?如果没有,最好是制作对象而不是属性包来存储所有数据。然后可以将这些对象持久保存到数据库中。
#4
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.
从绩效的角度来看,任何一种方法都可以。可能有多少艘船?所有数据都适合任何服务器上的RAM。