List:General Discussion« Previous MessageNext Message »
From:Chris W Date:January 14 2010 5:35am
Subject:Re: tmp tables
View as plain text  
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. 

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 
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 
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)
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 --
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

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 :)

tmp tablesVictor Subervi10 Jan
  • Re: tmp tablesBaron Schwartz11 Jan
    • Re: tmp tablesVictor Subervi11 Jan
      • Re: tmp tablesBaron Schwartz11 Jan
        • Re: tmp tablesVictor Subervi11 Jan
          • Re: tmp tablesKeith Murphy11 Jan
            • Re: tmp tablesVictor Subervi11 Jan
          • Re: tmp tablesJohnny Withers11 Jan
            • Re: tmp tablesmos12 Jan
              • Re: tmp tablesVictor Subervi13 Jan
                • Re: tmp tablesJohnny Withers13 Jan
                  • Re: tmp tablesVictor Subervi13 Jan
                    • Re: tmp tablesChris W14 Jan
                      • Re: tmp tablesVictor Subervi15 Jan