Welcome to WebmasterWorld Guest from 54.145.13.215

Forum Moderators: open

Message Too Old, No Replies

Database Structure Design

     
7:04 pm on Nov 10, 2006 (gmt 0)

New User

5+ Year Member

joined:July 18, 2006
posts:14
votes: 0


Hi, I'm building a database for my site using MYSQL with Phpmyadmin/PHP on IIS.
It's a very basic ecommerce site design. The database structure will look like this

one database with 4 table:
1. categories table.
2. product table.
3. order table.
4. orderdetails table.

very basic relation database. The categories table is for one-to-many, and the product table is for many-to-many with the category entity.

All table will start of with the top field reserver for the ID(primary key) for internal identification.

My question is.

Since I already have an ID field. Do I need a parent_id field in the categories table for it to work(query) with the product table. Or can I just create the categores table with something lookin like this.

ID Int null Primary Key
CategoriesA Varchar
CategoriesB Varchar
CategoriesC Varchar

and so forth.

8:25 pm on Nov 12, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 27, 2001
posts:2547
votes: 0


I think you just want a category_id field int he products table that stores which category the product belongs to. Then if you're displaying the product you also pull the category info using a join or by getting the id and pulling the category directly with your php. If you're displaying a category page, say for category 5 then you do a query on the category_id field in products to pull all products with category id 5.
10:43 pm on Nov 12, 2006 (gmt 0)

Full Member

10+ Year Member

joined:Nov 3, 2003
posts:324
votes: 0


Hi bauxyen;

I think you only need a separate category_id field if you plan to have deep category levels (ie. US > California) just to keep the taxonomy straight.

4:52 pm on Nov 13, 2006 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 13, 2005
posts:1077
votes: 0


category
-------------
categoryId
categoryName
parentCategoryId

product
-------------
productId
productName
(categoryId) if a product can only be in 1 category otherwise:

productCategory
-------------
categoryId
productId

order
-------------
orderId
productId
otherInfo

not sure why you need an orderDetails table as well, but maybe you do.

Chip-

6:23 pm on Nov 15, 2006 (gmt 0)

New User

10+ Year Member

joined:Jan 17, 2004
posts:27
votes: 0


I've always worked with a OrderItems/OrderDetails table. Why wouldn't you?
Just off the top of my head I can think of a few reasons.

order
-------------
orderId
customerID
orderDate
paymentType
lastModifed
orderStatus
dateShipped

orderitems
-------------
orderitemID
orderId
productId
quantity
inventoryStatus (out of stock, backordered,etc)
shippingAmount
discountAmount (coupons, discounts, etc)
productTotal

Just my .02.

10:06 am on Dec 4, 2006 (gmt 0)

New User

5+ Year Member

joined:July 18, 2006
posts:14
votes: 0


I want to thank everyone for helping me. I want to thank Steele for showing me a much better design. I actually use it right now. I like to plan ahead rather than having to go back and redesign my database later on. so thank you very much guys, and sorry for not answering back right away.