Welcome to WebmasterWorld Guest from 107.20.34.173

Forum Moderators: open

Message Too Old, No Replies

Database Structure Design

     

baxuyen

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

5+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

10:43 pm on Nov 12, 2006 (gmt 0)

10+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



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

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

10+ Year Member



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

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

5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month