Table Design Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

Table Design Question

Post by EricS »

I'm currently writing a shopping cart using PHP 4.0.6 and MySQL 3.23.32. I'm designing the tables and have hit a wall.

I'm think about putting customer info, minus order info, in one table and the order info, linked back to the customer table in another table. What's the best way to handle multiple items purchases? You never know how many items any person may order so how would you structure the order table to accomodate this? Try and somehow get all the items for each purchase into one record, or use a seperate record for each item and somehow link that back to a single order?

Any help would be greatly appreciated. :)
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Table relations like this

Customer---------<Order---------<Order_Item>---------------Item

Customer may have 1 or many orders
Order must have 1 and only 1 Customer

Order must have 1 or many Items
Items may belong to 1 or many Orders
This is a many to many relation, that is why we need the Order_Item table

table definitions would be something like:

Customer
customer_id
first_name
surname
etc


Order
order_id
customer_id
order_date
etc


Order_Item
item_id
order_id
quantity


Item
item_id
description
unit_cost
etc


This is a properly normalized data structure and will give you the best performance for a transaction system.

Hope this helps
Mike
Post Reply