Victor Subervi wrote:
You're so complimentary! Wouldn't it have been better to simply suggest FKs?
I think the reason the other poster was so harsh is because others have
suggested the right way to do it, if not in a lot of detail, and you
have just argued with them.
Since I'm not in the mood to work on my project I will give you an
overview of my shopping cart process and data structure.
I have the following tables.
Order
Payment
Cart
User
UserAddress
StuffToBuy
In the order table I have the following fields.
OrderID -- unique auto inc key
Status -- int to indicate if the order is in progress, processed
shipped etc. (use whatever set of statuses you need)
UserID -- this links to the user placing the order can be set to 0 if
the shopper doesn't want an account on this site.
OpenDate -- date and time the order was started (comes in handy to see
what is going on later down the road)
CloseDate -- date and time the order is finished.
TimeStamp -- auto update timestamp every time the order is modified.
(that way you can delete orders that are older than a certain age if you
like)
Any other date time fields to track with various status points are
reached depending on your needs.
Payment Table. This table exists separate from the Order table for 2
reasons. 1: I like to keep it separate. 2: if you provide the option
to pay over time for something like a service or whatever this structure
allows you to have multiple payment records for each order. Fields include.
PaymentID -- auto inc key
OrderID -- link to the order.
Status -- same as above but different uses.
Amount -- Amount of the payment.
SubmitDate -- Date and time order was submitted
ProcessedDate -- Date and time payment was processed (may be the same as
submitted if done in real time you decide if you need it)
Other Payment fields as required CC Number persons name address etc.
This way if the user making the order doesn't have an account on the
system you can store all that information in here.
TimeStamp --- just for good measure.
Cart table stores what people are buying or have bought depending on if
the associated order is closed.
CartItemID -- auto inc key
OrderID -- link to order. Before you can add an item to the cart you
need to create an order record first.
StuffID -- link to item they are buying.
Qty -- quantity they want to buy.
Price -- current price of item (save here because it can change over
time and you will want to know what they payed for it when the order was
placed.
Discount -- if you are giving some discount save that here or just leave
it as 0.
Cost -- What they will actually pay for the qty of StuffID they want.
(with Price and Discount you can see why the cost is what it is.)
DateAdded -- Date time they added the item.
You can calculate cost in a query if you like but storing it here makes
queries easier and I prefer to store an ID number for discount and look
up the amount of the discount in a discount table. Since the amount of
the discount in that record could change over time (bad idea to me but
it could happen) so that way having the final cost stored is handy. You
may want to structure it differently depending on the way discounts work
especially if there are qty discounts.
User Table
UserID -- auto inc key
UserName -- put a unique key on this. (using a separate UserID auto inc
key lets you change the user name if you want, which will happen. I let
users change their UserName on all of my web sites, don't know why
everyone doesn't do this)
FirstName
LastName
Email
Password
blah blah blah whatever you want to store about the user.
User Address this table stores various shipping and billing information
about your users so you can auto fill the payment table at the end of
the order.
UserAddressID -- auto inc key
UserID -- link to user.
AddressType -- I.E. Shipping or billing
Address Name -- so they can say if the shipping address is say for home
or work or aunt Jane's house.
AddressLine1 --
AddressLine2 --
City
State
Zip
whatever else you need like phone number etc.
Stuff To Buy table. This last table is very simplified and in most
cases would require other tables.
StuffID -- auto inc key
Name
Description
Price
QtyInStock
So now in case it's not obvious we are using foreign keys to link tables
in the following manner.
Order to Cart -- One to Many
Order to Payment -- One to Many or One to One depending on what you need.
User to Order -- One to Many
StuffToBuy to Cart One to Many
User to UserAddress One to Many
Just in case: Order to Cart -- One to Many means that "One" Order
record can link to "Many" cart records, this link is created by storing
the OrderID (foreign key) in the Cart table. Though it's not needed in
the system as described above a "Many to Many" link requires a table to
link to tables together having the two keys in it that relate to the two
tables being linked.
That's it for me tonight, everyone feel free to criticize at will.
Chris W
p.s. I don't mean to discourage the criticizing but a few notes about
the way I do things.
Keys are always auto inc. I never "attempt" to find some "real" data to
use as a unique key (very bad idea to me)
I always use the same name for my Keys in the parent and child table
(why do you think the "NATURAL JOIN" syntax exists :)
Ok let the criticizing begin :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
