homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL newbie question
not sure how many tables
mcjohnson

10+ Year Member



 
Msg#: 6327 posted 1:48 pm on Jan 3, 2005 (gmt 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.

Thanks!

Pat

 

DaButcher

10+ Year Member



 
Msg#: 6327 posted 2:10 pm on Jan 3, 2005 (gmt 0)

[tbl_items]
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

[tbl_manufacturer_id]
man_id (auto increment)
man_name (varchar(255))

[tbl_product]
prod_id (auto increment)
prod_name (varchar(255))
prod_man_id (int(1))

[tbl_condition]
cond_id (auto increment)
cond_title (varchar(255))

[tbl_user]
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..

mcibor

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 6327 posted 2:18 pm on Jan 3, 2005 (gmt 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,
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.

Good luck!
Michal Cibor

Dreamquick

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 6327 posted 2:25 pm on Jan 3, 2005 (gmt 0)

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

ManufacturerID
ProductID
ConditionID
Cost
ContactInfo

...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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved