|MySQL newbie question|
not sure how many tables
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.
item_id (auto increment)
item_sold (int(1)), default = 0
man_id (auto increment)
prod_id (auto increment)
cond_id (auto increment)
user_id (auto increment)
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..
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,
condition INT DEFAULT '0',
PRIMARY KEY(id) );
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.
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.