Link Customer Table to Orders Table in MySQL During Checkout

designrandom

Hello folks,

I'm building an e-commerce site for a cricket equipment distributer. When adding a cricket bat to your shopping cart, you select from some options:

Size - 5 options
Weight - 10 options
Edges - 3 options
Bow - 3 options
Handle Size - 2 options
Sweet Spot - 3 options

As you can see, there are hundreds of different possible combinations.

I have the following tables in the database:

Customer
Orders
OrderContents
Products
ProductOptions

The customer table is self explanitory (name, email address, postal address etc).

The products table holds the different cricket bats along with their descritions.

The orders table records the orders (OrderID, CustomerID, OrderContentsID, OrderDate)

The order contents has the OrdersID, the ProductID, ProductQuantity and OptionsID.

Options table has spaces for the options listed above.

In which order should I update the database as the customer checks out?

I don't need anyone to write any code - just need to hear how you would do this...

If a customer orders Cricket_bat_004 with short handle, size 4 and cricket_bat_008 with long handle, size 7 how do I tie the orders together in the database?

In the orderContents table, can I enter the productID's in a single field like this:
004, 008

and then enter the optionsID like his:
003, 109

If anyone has any ideas how to add the orders to the database, I would truly appreciate it.

Thanks

johanafm

designrandom;10929639 wrote:
how do I tie the orders together in the databas

The order contents has the OrdersID
And the question is not really asking the right thing: "How do I tie ordered items together in the database?", since you don't want to tie one order together with another order. They would have no relation to eachother.

In which order should I update the database as the customer checks out?

must exist before any order can be placed for this customer and product
[2]Customer
[1]Products

must exist when the user decides on what to order
[3]ProductOptions

actually creating the order
[4]Orders

assigning content to order
[5]OrderContents

designrandom

johanafm,

Thanks a lot for your reply. This has helped a great deal, but I'm still unsure of something.

What if the customer orders more than 1 cricket bat? In the orderContents Table, can I add more than one product ID and more than one Options ID and tie each product to a set of options?

Hope that makes sense...

Thanks again

johanafm

First off, this told me that you only allowed one option per item

The order contents has the OrdersID, the ProductID, ProductQuantity and OptionsID.

To allow multiple options per item, you'd need

orderContent(orderID, productID, ProductQuantity)
	primary key (orderID, productID)
option(optionID, type, text)	-- type is size, weight etc
	primary key(optionID)

orderContentOption(orderID, productID, optionID)
	primary key(orderID, productID, optionID)

Multiple products in one order is handled by the fact that your orderContent will look like

order		prod		...
1		1
1		10
1		2
2		1
3		1
3		2

Which tells you that the first order was for three products: 1, 10 and 2
Second order was for 1 product: 1
And last order was for two products: 1, 2

designrandom

johanafm,

Thanks for your patience with this. As you can tell ,I'm new to MySQL so thanks for explaining things further.

I'm not sure that I did explain things as well as I should have done, regarding the options.

I have a table for order contents and a table for options.

The options table look like this:

-----------------------------------------------
optionID | size | handle | weight | edges |
-----------------------------------------------
1        | L    |Short   |3lbs      |bowed|
-----------------------------------------------
2        | S    |Long    |3lbs      | bowed|
-----------------------------------------------

When a user checks out, I want to update the orderContents table as you did, but you can't have multiple sets of options per item - but if the user orders a number of products, each product will need to correspond to a set of options:

order		prod		optionid
1		1              1
1		10            2
1		2              4
2		1             6
3		1             8
3		2

When the users submits the checkout form, I insert the following:

Customer Details (into customer table)
Product Options (into options table, with unique ID for each set of options)
Orders (linking customerID to the Order)
orderContents (Which includes product ID's for each product orderd, but must also include optionID's for every product ordered)

How would you tackle this?

johanafm

Hmm, I'm still not entirely clear on the options. Are those options generic, i.e. the same options exists for all products, or do each product have their own set of options? I'll assume it is the latter

First off, to deal with many-to-many relation, n:m, for tables a and b, you need an extra table x. The result is a 1:n relation from a to x, and an n:1 relation from x to b.

To know what options are available for a specific product

CREATE TABLE productOption (
	productID INT UNSIGNED,
	optionID INT UNSIGNED,
	PRIMARY KEY(productID, optionID),
	FOREIGN KEY(productID) REFERENCES Product(id),
	FOREIGN KEY(optionID REFERENCS Option(id)
);

Product
id	name
1	iron bat
2	aluminum bat

Option
id	size	weight	...
1	L	50
2	L	2
3	S	25
4	S	1

ProductOption
productID	optionID
1			1
1			3
2			2
2			4

Thus, valid options for the light weight aluminum bat are options 2 and 4. When the user is presented options to select from you need something like

SELECT product.id, option.*
FROM product
	INNER JOIN productOption ON product.id = productOption.productID
	INNER JOIN option ON option.id = productOption.optionID

This will get you all options for each and every product. You can of course append "WHERE product.id = 2" to get only options for the aluminum bat.

To deal with the selected option for a product in the actual order, things are simpler 🙂
All you need in orderContent is orderID, productID and optionID (for this purose. the other things should of course still remain in this table). But, since it is possible that I want to order the aluminum bat in both large and small size in the same order, you need to have a primary key on all these columns in orderContent, instead of just on orderID and productID:

PRIMARY KEY(orderID, productID, optionID)