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
I would refactor your code if:
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?