[thelist] Database Schema Suggestions

[Luther, Ron]

Burhan Khalid asked:

>>   I'm having a bit of a problem trying to sort out how to store 
>>information in a database that does not have a defined size. An item 
>>that can have /n/ amount of properties/attributes for example.

Hi Burhan,

Interesting problem.  I believe this usually comes up in cases where 
the attribute values (while perhaps variable by product) are fairly 
fixed in number and value. In that variation, I think this kind of 
thing comes up a lot in manufacturing (BOM) and commerce type apps. 
e.g.

Product Table:
SKU     DESCRIPTION
----    -------------
1352    HAT WITH EVOLT LOGO
1355    SHIRT WITH EVOLT LOGO

Attribute Name Lookup Table:
ATTRIBUTE_NO    ATTRIBUTE_NAME
------------    --------------
   1             COLOR
   2             SIZE
   3             SLEEVE LENGTH

Attribute Value Lookup Table:
ATTRIBUTE_NO    ATTRIBUTE_VALUE     DESCRIP
------------    ---------------     -------
   1                  1             WHITE   
   1                  2             BLACK
   2                 22             LARGE
   2                 23             LARGER
   3                 40             SHORT SLEEVE
   3                 41             LONG SLEEVE


So a customer orders a new classy evolt wardrobe. The order can be 
stored in an order table, (probably separate header and detail tables), 
and the detailed information necessary to fulfill the order can 
be stored in a separate (multi-row per order line) 'features' table. 
[The multi-rows are (I think) easier to work with than comma lists.]


>>   What I'm trying to do is create an automatic form generation system 
>>for an event scheduling application. The administrator will create an 
>>event, and if that event requires registration, they would then create a 
>>registration form. The problem I'm having is how to structure the 
>>database so that I can store the form data ... specifically, any number 
>>of fields that the admin might want. It would be moot to limit the form 
>>to /x/ amount of fields.

Your case is a little different since the values table may be less likely 
to have fixed entries.

However, if you think of an event as a product, then I think the example still 
holds. If you're attribute values aren't "reuseable" from event to event you 
may have to add an event id to the attribute value table.

Event #17 --- "Burhan - Live in Concert!"

Attribute 1 - date
Attribute 2 - location
Attribute 3 - venue size (available number of tickets)
Attribute 4 - # of ticket price levels

Price level 1 -- $100
Price level 2 -- $250
Price level 3 -- $400


Sorry ... ran out of time to play with it.

Hope this helps some!


RonL.