Simple MySQL database structure question

Question

This is something I should really know, but I haven't done any db work for quite a while and just wanted some clarification on a structure for a simple mysql database.

The basic premise is a shopping cart - so we have catalogue, customer and orders.For now, the customer isn't going to sign up, they will enter details every time.

My main struggle is how to structure the customer and order table as obviously it would be good to have these separate.

Is the best method just to have the customer table with an fkey to the order table, and the order table simply references the item in the catalogue with another fkey?

Any clarification is much appreciated.

Simon · Accepted Answer · 2011-04-25 13:10:07Z

2

You have 4 tables at the very basic level: users, basket, items, basket_items.

Orders would work just like basket and basket_items so let's keep it simple for now.

A Record in basket_items is just a basket_id and an item_id - it's a link table used for resolving this kind of many-to-many relationship (each basket has many items, each item can be in many baskets).

The basket or orders table, as you say, just have a foreign key to the customers table - each order is by one, and only one customer.

answered Apr 25, 2011 at 13:10

Simon

2,00815 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon · Accepted Answer · 2011-04-25 13:48:59Z

0

Maybe you can have an orders table, in which new user session (or log in, later) a new order will be created. It will contain the customer_id, the quantity of items, the item_id (fkey) and the total (calculated dynamically if you wanted to)

edited Apr 25, 2011 at 13:48

Simon

2,00815 silver badges21 bronze badges

answered Apr 25, 2011 at 12:39

Eric Frick

8572 gold badges7 silver badges18 bronze badges

3 Comments

user319940 Over a year ago

The bit im struggling with is there can be more than one item in the basket and I don't want to be comma separating them.

Simon Over a year ago

The orders table can't contain IDs of all the items. You need a separate order_items table to link the two.

Eric Frick Over a year ago

of course it's not convenient, but it will contain the item_id linked with the item table and the quantity of the order.

Collectives™ on Stack Overflow

Simple MySQL database structure question

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related