Quick q about a database for a shopping cart

Q_Ball

Just a quick question guys,

how should an order table be done for a shopping cart?

My previous database had a cart table with cartID as the primary key and UID as a foreign key, with a bookID field. This lead to one database entry per book ordered per customer.

My new table has an order ref(primary), UID(foreign) and order date. I want all the books ordered to be put into one database entry under the one order ref. I can only really think of 2 ways of doing it which is why i'm here.

1st way:

cap the order at 10 books

2nd way:

add a field for every book to the table, using an external parameter to index the field :rolleyes:

i.e. (pseudocode)
i = 0
add field bookID(i)
i++

The 1st idea is the easiest, the 2nd way would look more impressive on paper i reckon.

what would you guys do?

damnyanks

Link the books ordered to a order number?

stevenmu

This may be the same your option 2 (I'm not really sure what you're saying with that). The way I'd be inclined to do this is to have a second table OrderDetails or OrderLines or something like that. You could implement it a few ways.

You'll need to have an OrderRef field as a foreign key to tie each record back to the Orders table, you'll also need a field to hold the book ID or ISBN or whatever you use. You could optionally have a LineNumber field to keep each line in the order in order (this could also combine with the OrderRef field to give you a nice primary key). You could also optionally have a quantity field if they can order multiple copies of the same book, if you don't have this then you'll need to have a seperate OrderLine record for each copy of the book they order. Another option is a field to hold Price of the book(s). You could of course just read this from your Books table as needed but this way let's you arbitrarily assign discounts on a line by line basis or even add a line which has negative prices (for credit notes etc).

Then, any time you have an Order record, which now only hold info which applies to the whole order, such as customer info, order date etc, you can find all the OrderLine records which have the same OrderRef to see what's actually on it. It goes without saying this field should be indexed to keep everything running quicky.

edit: Oh wait, I get your second idea now, that's horribly inefficient. You'll have to change your table everytime you want to add new books, even worse if you remove books you'll similarly have to remove the columns, destroying past orders.

bonkey

Q_Ball wrote:

I want all the books ordered to be put into one database entry under the one order ref.

Why would you want to do that?

The whole point of a relational database and normalisation is to get away from storage structures like what you dscribe, because of exactly the limitations that you're trying to work around.

What you want is an Order table, which contains a single Order, and then an OrderItem table, which contains one entry per item per order. this gives you a 1-N relationship, which allows you have as few or as many orders as you like.

This is more or less what Stevemnu suggested, only I'd probably have OrderId & ItemId referencing records in the Order and Item tables, and the two fields together as the Primary Key.

Note: buying multiple copies of the same item in a single order could not result in multiple entries in OrderItem using this approach....as it would violate my suggested Primary Key....which is where a "QuantityOrdered" field would come in useful...

AS a general rule of thumb...learn the rules of normalisation.....design a database to be fully normalised (to 3rd Normal Form, at least)....and then consider where there are aspects which should be de-normalised. Only de-normalise when and where there is a compelling reason to. What you're describing doesn't seem to be a good candidate for denormalisation.

jc