help with a db layout

[glennnall]

i've attached an image of a sample of a large dp as put together by my client: items, part numbers and a series of available options and their respective prices all in one table.

 

i'm humbly asking if someone could show me how a couple of relational tables would be built with this data so that i'd be able to query and render an item with its respective options - this db contains almost 1000 products with perhaps 100 columns of possible options/prices versus the few that i've shown here. i'm just needing a way to get started, not knowing if i should make ONE other table with all the options or a table for each column/option represented here... and then how to query an item and its options...

 

i REALLY appreciate any help offered.

 

thanks

GN

 

[attachment deleted by admin]

[DavidAM]

It is very difficult to design a database based on a few (incomplete) potential entries. It is also very difficult to design a database without knowing how the data is to be used.  However, I'm going to tell you the absolute perfect way to design this database ...   

 

Looking at the image, I would convert that sheet to 3 tables (of course, I tend to over complicate things, so someone else may have a different idea).  I would not even consider creating a single table with all of those columns.  That would not be "normalized" and would not really be relational (there's nothing to relate to), it would just be a spreadsheet.

 

Table 1: Products

ID             INT UNSIGNED AUTO    Primary Key
PartNo         VARCHAR
MFR Style      VARCHAR
Brand Style    VARCHAR
Description    VARCHAR

• Even though you already have ID's assigned, I would make the column AUTO INCREMENT -- to support new products being added.  You should be able to insert the data with the existing ID values - and I would if the customer is used to using these values currently.
  
• PartNo should be a unique index as well
  
• I don't see any uniqueness (except id and part number) in the data you provided, but I have included a description field and you may have other data elements that apply only to the product itself, as well
  

 

Table 2: Options

ID            INT UNSIGNED AUTO    Primary Key
OptName       VARCHAR

• These are the options across the top, just the names: i.e. Brass Locks, etc
  
• This table is just for the relationship to the third table
  
• Other data specific to the option itself could be included here
  

 

Table 3: ProductOptions

ProductID    INT UNSIGNED    Foreign Key to Products
OptionID     INT UNSIGNED    Foreign Key to Options
Price        DECIMAL

• An entry in this table means the option is available for the product and gives the price
  
• Note: IF the price is always the same regardless of the product, the price could be moved to the Options table.
  

 

This design does not address potential issues. Such as,

 

1) Does the product support both the 3x5 Card Holder and the 4x6 Card Holder at the same time (i.e. are some options mutually exclusive)

 

In this case, I might add an OptionGroups table to define group names. Then include the GroupID in the Options table.  Then the application can ensure that the user picks only 1 item from each option group. Or something along those lines.

 

2)  What about the flip side of that, do some options require the presence of another option (i.e. do I have to select some style of Rims in order to put some style of Tires on my Car?)

 

This would require some deeper analysis.

 

[glennnall]

i'm very grateful for your help, DavidAM -

 

very basically this is simply a (MUCH LONGER) list of options a potential buyer of an item would have to choose from, i.e.

 

if someone chooses to view part # ZRC0606-0802, then the options that would be rendered AS AVAILABLE would be

Brass Locks 9.95

Inner Lid  N/A

Hinged Lid FREE

Card Holder 21.06

 

if they viewed #3i-0907-04, the only option available would be

Brass Locks 9.95

 

the important thing, and a question that you asked, is that the price for each Option DOES VARY with any given item, so Brass Locks Option for one part number might or might not cost the same as the same for the next part no.

 

here's an example of what's been done with a very UN-normalized database:

 

go here: http://south-pak.com/search/ and search    AL1010-0904  in the first search field, "Stock Case Search By Part Number" - you'll see what i'm talking about. ALL the available options are listed at the bottom -

 

thanks so much for your help - i'm looking forward to learning more of this stage of database design from you...

 

glenn

 

[glennnall]

oh, and the id is autoincremented. these are just excerpts from the table with 800 records. and more than 100 options, not just these five or six...