[thelist] assistance designing schema

Jeremy Weiss eccentric.one at gmail.com
Thu Jan 7 16:32:38 CST 2010

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. For example, they want a manager to be able to add a item type of
'computer' and add custom fields for that type (model, serial, mac address,
etc) and then add type 'tools' and have a different set of fields and then
add 'parts' and add another set of fields.

Personally, I rather just meet with everyone involved and determine what
fields are needed and just build that, but that's not an option. So, how
would I structure the database to pull this off? Here's what I've come up

- item_id (PK)
- type_id (FK)
- customfield01
- customfield02
- customfield03
- customfield04
- customfield05
- customfield20

- type_id (PK)
- name
- fieldname01
- fieldname02
- fieldname03
- fieldname04
- fieldname05
- fieldname20

I'd like to get some feedback on it before I dive in headfirst and regret it
later, if you fine folks have a few minutes. Anyone see any potential
problems with this route or know of a better way to do it?



More information about the thelist mailing list