Hi Everyone
This is my first post here. I am just planning my first serious DB and I am stuck and don't really even know what keywords to search for to solve my problem. Please help me model this.

I suppose if we had an ice cream store we could model a flavour table and a container table. If you had vanilla ice cream from the flavour table you could choose plain cone, flavoured cone or cup from the container table. In this case the options that accompany vanilla are finite and known, in this case 3.

What I can't figure out is a good way to model this if the options to the original product do not have a consistent value. I sell laboratory instrumentation and I would like to offer parts as well as the instrument itself. In some cases there might only be 3 additional parts to offer but in other case it might be close to 100. In our ice cream DB we knew we could get by with four columns ID, plain cone, flavoured cone and cup. If I don't know how many parts will be offered with a given instrument do I need to create the maximum number of columns? If I only offer 3 parts with one instrument do I need to have 97 empty columns if I want to have another instrument with 100 parts?

Thanks for reading my long post-Patrick

this is a poor design -- you don't use columns for the variations, you need to use rows

so if you have 100 parts, you have 100 rows

simple, yes?

Thanks for your response r937.

I suck at forums, I probably did not provide enough information...

I would like to offer many instruments each with potentially many parts. If I model the options in rows would I not need a table for each instrument? If that's the case I can do this, I am just wondering if it will be hard to maintain the DB with 100+ tables.

Thanks again-Patrick

Hi Patrick... sorry but my english... this is very bad, and may be not understand you.

But.. you have a ice cream store (please send me 1 kilo of choclate ice cream)... lol

and you need a sales model, when you have in each sale item a combination of products, for example vanilla ire cream with a cone, in this case 2 productos..


a simple model...

salesTable:
idSale
dateSale
(and more data of the each sale)

detailSalesTable:
idDetailSale
idSale
priceDetailSale
(and more data of the each detail sale)

productsDetalisSalesTable:
idDetailsale
idProduct
price
(and more data of the each product detail sale)

productsTable:
idProduct
price
(and more data of the each product sale)

in this case the table products contains ice creams, cone, etc, etc, etc
tell me if this "model" help u.
and very look...

Gracias Achiola!

Yes this helps, thanks. It's helping me see new things. Do you think that this would work?

If I have multiple instruments to sell each with multiple parts could I do this

Instrument table
Parts table

let's say we have two instruments in the instrument table:
cheap instrument
expensive instrument

and then in the parts table we have two columns
instrument name and parts name with four rows
cheap instrument keyboard-part
Expensive instrument door-part
Cheap instrument tubing-part
Expensive instrument power cord-part

Could I enter a bunch of parts and then just group them by instrument name and then do some sort of join back to the instrument table?

Thanks again Achiola, your English is great, mine is not and it is my first language :-(

Achiola might be busy now. Could anyone else let me know if this is the right approach?

Thanks in advance-Patrick

yep, this model can do it, but not with names columns, for it have the ID.
for example a autonumber column.

And then U can join all tables.

I suspect you still haven't 'got' it yet
so you have a product table...
a product is made of many parts.....
you have a another table beneaths that which identifies what parts go into that product.
however taking a step back a part in itself is also a product (how else could you treat somethting, say a power cord, which may be sold separately to the main product, or is common across a range of products

so you may have
table: products
ID 'PK (Primary Key)
description
blah-di-blah

table: goesinto
ProductId ' (FK: foreign key) identifies what product/part is
ParentId ' (FK: foreign key) identifies what this prosuct/part is used in
blah-di-blah
..you'd put other columns here say for example the quantity of product_id used in parent_id. in this case both ProductID and ParentID are the Primary key in this table as the combination of the two elements defiens what makes this row unique (ie you can only ever have one productID / parentID combination.. you can have lots of productid's matched with one parentid, you can have lots of papentid's defined with different productids (ie what parentid is comprised of. there is nothign stopping you rolling up producs into sub assemblies eg product (parentid) X is made up of prodcuts (1,5,2 & 10), product ZQ is made up product A,F, C, X. its also possible for say a product to be used in more that one sub assmebly ( eg you may decide to standardise on a M8 Hex bolt and that may be used for sub assemblies and the final assembly

if you have different versions of the same part you may need to define a verision/build id as part of the primary key of your goesinto table. eg version 1 had ......., version 2 replaced the IEC cord with a Telefunken cord
productid ' identifies what product/part is
parentid ' identifies what this prosuct/part is used in
BuildNo ' identifies the specific build of this product

again taking a step back think about what you are proposing
you are making a direct map between each product (the cheap and the expensive). for data modelling purposes that doesn't matter.. but what happens if say you introduce 2 more instruments, a 'value' instrument and a 'luxury' instrument. you don't want to have to revist the data model and make chanegs to the application. you want to make htose changes in data only. its part fo the science of problem ownership, when the user makes a change to their business practices you need to do as much as possible to make certain its the users problem to make data changes, than your problem to make db & application changes.

I think you may well benefit form reading up on normalisation
there are 2 excellent web pages on this
Fundamentals of Relational Database Design -- r937.com
and
The Relational Data Model, Normalisation and effective Database Design

HTH