[thelist] assistance designing schema

[Paul Bennett]

Hi Jeremy,

The most obvious thing I can see is to remove your custom field columns
from the item table and place them in their own table.
The relationships make more sense when designed as parent > child eg:

- item can have many customfields (one to many relationship between item
and itemfields)
- type can have many items (one to many)
- type can have many fields (one to many)

so the schema would then start looking more like this:

type
---------
id
descriptor

typeField
---------
id
typeId
descriptor

item
--------
id 
typeId
descriptor

itemCustomField
--------
id
itemId
descriptor

In this way, you can have as many children of each parent as you want
and don't need to change your entire db structure and application when
someone decides they actually want custom field number 21 for their new
item (you know it will happen :) )

HTH,
Paul

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Jeremy Weiss
Sent: Friday, 8 January 2010 11:33 a.m.
To: Evolt
Subject: [thelist] assistance designing schema

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
with:

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

types
- 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?

Thanks.

-jeremy
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester
and archives of thelist go to: http://lists.evolt.org
Workers of the Web, evolt ! 

www.mch.govt.nz  -  www.teara.govt.nz  -  www.nzhistory.net.nz  -  www.nzlive.com

The information contained in this email message does not necessarily reflect the views of the Ministry for Culture and Heritage and may contain information that is confidential or subject to legal privilege. If you are not the intended recipient and receive this email in error: please notify the Ministry for Culture and Heritage by return email or telephone (64 4 499 4229) and delete this email; you must not use, disclose, copy or distribute this message or the information in it.

PLEASE CONSIDER THE ENVIRONMENT BEFORE YOU PRINT THIS EMAIL