Database Structure Design

[baxuyen]

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.

[physics]

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.

[stajer]

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.

[carguy84]

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-

[RWSteele]

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.

[baxuyen]

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.