[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