Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL newbie question

not sure how many tables

1:48 pm on Jan 3, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:July 8, 2004
votes: 0

Hello all,

I am setting up a website for a client where potential customers need to be able to search for used parts on a database, and also add their OWN to the db.

Three of the variables will be fixed: Manufacturer (4 choices), Product (four choices) and condition (new, used, refurbished) the last two need to be cost and contact.

So, let's say I have a used Smith Widget and I want to list it in the database. I need to be able to go to the "add an item" page, and in the first three drop down boxes, simply choose from one of the four manufacturer, one of the four prouduct types, select a condition, add the cost and my name/email/phone.

My initial question is about the tables. Do I need one table for each of these categories? Or one table with 5 columns? I am quite a newbie but learning fast and any assistance anyone can provide would be awesome.



2:10 pm on Jan 3, 2005 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 2, 2004
votes: 0

item_id (auto increment)
item_manufacturer_id (int(1))
item_product (int(1))
item_condition (int(1))
item_added_date (timestamp())
item_added_by_id (int(10))
item_sold (int(1)), default = 0

man_id (auto increment)
man_name (varchar(255))

prod_id (auto increment)
prod_name (varchar(255))
prod_man_id (int(1))

cond_id (auto increment)
cond_title (varchar(255))

user_id (auto increment)
user_fnam (varchar(255))
user_lnam (varchar(255))
user_phone ... etc etc

then you can fix it with natural joins I'd guess.

SELECT * FROM tbl_items, tbl_manufacturer_id, tbl_product... where ... = ... AND item_sold = 0 ORDER BY..

2:18 pm on Jan 3, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 26, 2003
votes: 0

Dear Pat!

It depends on the product. If one product hold values for manufacturer, product, condition, cost and contact, then you should make one table with 5 rows.

If manufacturer/product/condition is separate , there should be 3 tables with proper name, cost and contact.

But I think that you have something like this:
One manufacturer makes all 4 choices of product, which can be new, used or refurbished. Then the first option is right - with one table.

In mySQL it would be like:

CREATE TABLE product (
id INT NOT NULL auto_increment,
product VARCHAR(50),
manufacturer VARCHAR(50),
condition INT DEFAULT '0',
cost DOUBLE,
contact TEXT,
date_oc DATE,

to tell the truth product can also be INT, 0-3 for each of the choices but it's harder to add a new product.

Good luck!
Michal Cibor

2:25 pm on Jan 3, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 25, 2002
votes: 0

Largely depends how complex you want to make it. You could just have one "Parts" table with;


...where all the IDs would be hardcoded into your application. That would make for a very simple table with no other tables involved in the relationship, making queries and searches very easy to write.

The next "step" of complexity after that would be to have separate "lookup" tables for Manufacturer, Product and Condition allowing you to create a relational structure and making it a lot easier should you ever want to add extra choices to your "standard" selections.

Beyond the above the only extra thing I can think of would be to take the contact information off the main table and place it in its own table, linking the two with a user id. If you expect that you'll have a "regular" userbase then this would save you duplicating contact information every time.

- Tony