[thelist] assistance designing schema

Luther, Ron Ron.Luther at hp.com
Mon Jan 11 13:07:04 CST 2010


Jeremy Weiss asked about inventory systems ...

>>I've been asked to build an inventory system. At first the requirements
>>seemed simple enough. Users should be able to check things in and out, add
>>new stuff, delete old stuff, etc. But then there's a curve. They want the
>>users (managers, not the grunts) to be able to add tool 'types' with custom
>>fields. 


Hi Jeremy,

Doesn't sound like a problem ... unless, of course, they ever want to see or use that information again!

;-)

I would suggest a "middle" approach for you to consider.  

Naturally, it's best (and easiest) to get firm requirements for the 'main line' functionality and reporting.  But I think you might be able to meet some of their customization requirements through use of a "attribute - value" set of tables.

Let's say you have your "main" inventory table(s) with part_id, description, location, cost, quantity, supplier info, etc.

Now you can create some "attribute - value" tables.  E.g.

Attribute_Table:

Attribute_Id  	Attribute_Desc
010			Color
111			Localization
404			MAC_Address



Value_Table

Attribute_Id	Value_Id	Value_Desc
010			010		Red
010			011		Blue
111			010		English
111			011		French


[Yeah - the first column *seems* unnecessary ... you would think that you should be able to define the values independently, but once you get 12 different customer organizations adding these custom attributes you will soon find them stepping on and overwriting each other's values so I usually leave it like this to keep things simpler.]


Now you can store the custom data in a third table:

Part_Id	Attribute_Id	Value_Id
FN1101#6	010			011
FN1101#6	111			011
GN1203#8	010			010
...


That kind of structure is flexible enough to allow the customer to get into trouble ... but structured enough to allow you to build reports and interfaces and other 'neat stuff' that might actually use the custom data once the customers get it properly entered.


Best of Luck and HTH,
RonL.



More information about the thelist mailing list