homepage Welcome to WebmasterWorld Guest from 54.227.20.250
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 / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Database Structure Design
baxuyen

5+ Year Member



 
Msg#: 3152890 posted 7:04 pm on Nov 10, 2006 (gmt 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.

 

physics

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3152890 posted 8:25 pm on Nov 12, 2006 (gmt 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.

stajer

10+ Year Member



 
Msg#: 3152890 posted 10:43 pm on Nov 12, 2006 (gmt 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.

carguy84

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3152890 posted 4:52 pm on Nov 13, 2006 (gmt 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-

RWSteele

10+ Year Member



 
Msg#: 3152890 posted 6:23 pm on Nov 15, 2006 (gmt 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.

baxuyen

5+ Year Member



 
Msg#: 3152890 posted 10:06 am on Dec 4, 2006 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
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