one products table or more than one?

[inusrat]

This is rather dumb question.

Suppose there is an electronis store with all sort of products.
Does it make sense having one product table for all the products? is it practically possible?

Other than basic info that each products has like, item_no, description, category etc. Table need to have products specification which is different from category to category.

For example:
Cell phones has specification like following and need to have these fields in the table.

General Network
Size Dimensions:
Weight:
Display

on the other hand VCRs has spec like

TV System
Audio Track
Heads
Freq Response

What will be the right approch

Thanks

http://www.786Marriage.com

http://www.amasspharma.com

http://www.ambdex.com

 

[r937]

do a search in your favourite search engine for "subtype/supertype"

you have a choice --

1. one table for everything, with nulls in those columns that don't make sense for each type of product

2. one table for common info, separate tables for the additional stuff, one table per subtype

3. sparate tables for everything

myself, i prefer one table, unless there are, you know, too many attributes that are different, then i'll use 2 (but never 3)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[willir]

inusrat

Not a dumb question! I wrestled with this same issue a few years back. In my case, it was IT inventory, but same issue -- some things are common, others are not.

tblProductMaster
ProductID - primary key
ProductType - text, short codes for TV, DVD, CD, Amp, etc
SerialNumber
Manufacturer
Model
Weight
...etc

And then have one-to-one relationships with product type tables that capture the specifics for the product.

tblProdTV
ProdID - primary key
ProductID - foreign key to ProductMaster table
ScreenFormat
TubeTechnology
...etc

tblProdDVDPlayer
ProdID - pk
ProductID - fk
Resolution
...etc

You get the picture.

Presnetation
Presentation is tough. I used a multi-tabular form with the ProductMaster details on top, above the multi-tabular form. The mulit-tabular form would have various subforms, one for each ProductType. VBA coding would use the OnCurrent event procedure and hide / unhide forms or tab pages depending on the ProductType.

For example, with a DVD Player product type, the DVD page / subform would be visible, the CD Player, TV, etc would not be visible. However, the subforms with the manufacture information would be visible for all product types.

This is just a big-brush sketch, but hopefully you understand.

Richard

 

[inusrat]

Thanks to both of you. I think what you mentioed is the right approch, one master table with common infornmation and then other tables for specific informations , with one-to-one relations.

But I may have to take rather unprofessioanl approch and
just use one table having all the fields for all the products types.


The reason is once I enetered the data my cleint they will be enetring the data for products in the tables on their own.
I think it will be easier for them if there is one table. They will probably get little confused if they have to enter data in different tables for different catrgories...

So I may have to scarfice my database design to make things easier for them.....

Thanks

http://www.786Marriage.com

http://www.amasspharma.com

http://www.ambdex.com

 

[johnwm]

You could make a form for them to enter their new data, with a limited choice (eg ComboBox) for Product category required first, then giving them the appropriate combos, textboxes and checkboxes to suit the product category selected.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[RiverGuy]

Here's another option. I don't like it too much, as its not strong typed. But it does provide some flexibility.


Product
------------
ProductID
Make
Model
Description


Details
------------
DetailID
DetailName (i.e., Resolution)


ProductDetails
------------
ProductID FK
DetailID FK
DetailValue

 

[inusrat]

You are right I can have table relation and makes form for them to enter data. My initial plan was to let them just logon at the hosting company and enter the data straight there using the phpMyAdmin 2.5.1 , that would have saved me lot of time...

But I think i should make forms for them.........

thanks

http://www.786Marriage.com

http://www.amasspharma.com

http://www.ambdex.com

 

[willir]

Entering data straight into tables is dangerous...
The Ten Commandments of Access
See #2.

I understand your reluctance of using different tables, and making it easier for the end user. My solution would tough on you the developer because it requires work, but this does not mean it would tough on the end user.

If you are going to have the end user enter data directly, and/or use one table, then you may want to consider using a spreadsheet application.

 

[jollygood]

Hi,
There is yet another aproach than mensioned.
It's not allways possible to use, but it's extremly flexible and .. quite demanding for the fe developer.

Use 4 tables
my_products : common data storage for id etc.
prod_id
etc...
my_category
cat_id
cat_name
my_fields : fields descriptions holding data like;
field_id
cat_id
field_alias
field_datatype
my_data : table for storing single data items
data_id
field_id
field_data referably a text field

This aproach is most useful when field changes are needed in realtime by the users.
The tric is to use TRANSFORM queries based on joins from thouse tables for retreival.

jollygood