SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: please comment on this db design
-
Sep 13, 2002, 08:00 #1
- Join Date
- Jun 2001
- Location
- Israel
- Posts
- 184
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
please comment on this db design
I would like some comments on a database sructure I am working on.
Listed below are the tables in bookstore database which deal with shipping data.
Its not the first thing I came up with and would like some comments before finalizing it.
I'd be happy to post the entire db design on Sunday. But I thought it would be easier to do a thorough job chewwing this one bite at a time.
A few factors that went into this design;
The people entering books will only know the approximate weight of the books, refered here as issueDetailsID.
These tables have data telling us which books are shipped to whome and by what method.
Code:orders ------ orderID issueDetailsID quantity orderDateEntered shipmentID accountID paid shipments ---------- shipmentID shipmentMethodID addressID dateEntered dateSent comments user inputed shipping instructions and admin inputed "on the way to the post office" stories.
These tables store information we need to calculate how much to charge the customer for shipping any given shipment.
Code:shippingMethods --------------- shippingMethodID shippingMethodName shippingScales (junction table) -------------- shippingScaleID shippingMethodID bookWeightCodeID shippingPriceFirst shippingPriceAdditional additionalStartAfter bookWeightCodes ----------------- bookWeightCodeID bookWeightCodeIDName beightMin weightMax
-
Sep 13, 2002, 09:30 #2
- Join Date
- Mar 2002
- Location
- Svíþjóð
- Posts
- 4,080
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
My suggestion:
Create two more tables:
** orderhistory
orderid
eventid
date
comment
** orderevents
eventid
description
Example, one order can have these events:
created
confirmed
paid for
sent to customer
on the way to post office
lost in space
...
-
Sep 13, 2002, 10:33 #3
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
OK, you're gonna love me, but I see a lot of holes in the definitions you've defined so far (and we haven't seen
the whole kit and kaboodle yet....) Let me ask some questions of you:
[list=1][*]Is there ever more than one item per shipment or is it limited to one book per order?[*]Is there ever a reason you would ship items more than one way?[*]Can there be more than one recipient for an order?[/list=1]
I am going to make some assumptions (yeah I know what they say about assumptions, but I do that well enough
on my own) My assumptions are:
- There can be more than one item ordered at a time
- An order is only shipped one way
The problem I see with your approach is there is no driving element; no "parent" table if you will.
I would try to start with the most common component, which based on the limited information you've
provided sounds like the orders table. There is only ever one order, but each order can have multiple
items on it (see assumptions defined above). As such, I would define the following tables:
Code:Legend (PK = Primary Key, FK = Foreign Key) Order ----- OrderID - PK of course AccountID - FK to the Account table DateEntered - Note: Could just be included in the OrderStatus table - defined below. your choice... AddressID - Might not be here. See note below... ShipmentMethodID - Might not be here. See note below... Comments - For Specific Instructions for the order OrderItems (PK = OrderID + ItemID) ---------- OrderID - FK to the Order table ItemID - FK to the Item table Quantity - Quantity ordered Item ---- ItemID - PK ItemDesc - Description of the book ItemWeightCodeID - FK to the ItemWeightCode table ItemWeightCodes ----------------- ItemWeightCodeID - PK ItemWeightCodeDesc - Description of the item weight code weightMin weightMax
You will undoubtably have standard statuses (ordered, filled, shipped, received, etc). This is an easy
way to define the table. And since there can be multiple statuses for a single order, we'll also need an
OrderStatusHistory table. Let's define them since they are related:
Code:Status ------ StatusID - PK StatusDesc - Description of the status type OrderStatus (PK = OrderID + StatusID) ----------- OrderID - FK to the Order table StatusID - FK to the Status table StatusDate - Date this status was reached
need to look at your process before determining if this approach is right. I am going to make an assumption that
the weight of a book is always consistent and should always be applied to the item. Now, as I stated before, I
went on the assumption that one order can only go to one location. If that is wrong, this part will have to change
some. I would design this next part like this:
Code:ShippingMethod --------------- shippingMethodID - PK shippingMethodName - Description of the Method ShippingScales -------------- ShippingMethodID - FK to the ShippingMethod table ItemWeightCodeID - FK to the ItemWeightCode table BasePrice - This is more descriptive to me, change it back if you'd like BaseLimit - Limit for the base price OveragePrice - Price for going over the limit
Last edited by DaveMaxwell; Sep 13, 2002 at 10:38.
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Sep 15, 2002, 05:06 #4
- Join Date
- Jun 2001
- Location
- Israel
- Posts
- 184
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Dave,
I think we have some semantic problems here. What you are doing with the orders table, I was doing with the shipments table. For me 5 Bibles, 3 Korans, 2 ASP Programmers's References would be one shipment. "5 Bibles" would be an "order" for me.
Also, another semantic point of confusion here might be issueDetailsID. By this I mean itemID, in other words, a particular book. Sorry for not explaining that before.
One practical difference between our designs is that you put accountID in the parent table and I put it in the child table. In my design, the account number will apear with each item that is ordered. In yours, it only appears in the listing representing all the items purchsed together.
Also, If someone requests to send some of his books to one address and the others somewhere else, I will be able to associate different books to different addresses with the shipmentID fk in each order listing, which is not possible in yours, consistent with your assumptions.
But now that I'm looking at my tables again, I see no reason for a dateEntered field in both orders and shipment ables. I wonder what I was thinking?
jofa,
Your point is well taken. I might incorporate it. But I'm not sure if I should since I don't know if I'm really going to use this db for information about shipments, after the order has been processed. I think we may provide that information to the user by email when required, without recording it on the web site. I just threw the "comments" field in there just in case we might decide to use for our own information, as opposed to the customers.
-
Sep 16, 2002, 23:41 #5
- Join Date
- Jun 2001
- Location
- Israel
- Posts
- 184
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
now for user information tables
Thanks for help are the tables I posted. Here's another chunk of the design.
What we have here are the tables that store information about website users, mainly customers, their accounts, login keys, group affiliations, discounts, page views, primary addresses and shipping addresses.
One thing I wasn't sure about was whether or not to store discount information in the users table in addition to the customerGroups table as I have done here. An individual user may have his own discount details. Lets say we want to compensate him
for a late delivery by giving him a 15% discount for one month. Or should I make another table, "discounts" where I define discount properties which can be used for individuals or groups.
As I write this question the issues are already becoming a bit more clear to me.
Suggestions and criticism welcome!
Code:users -------------- userID pk userNameFirst userNameMiddle userNameLast userEmail userPassword usePhoneAreaCode userPhone userPhoneCell administrator userDiscountPCT userDiscountStartDate userDiscountExpireDate customerGroupID fk accounts -------------- accountID pk userID fk CCType CCNumber CCDateExpires accountDateEntered valid addresses -------------- addressID pk recipientFullName where null, listing represents a primary address, the one given along with credit card account information. For this Address, the name was written to the name fields in the related users field. pob street city state zip country userID fk customerGroups -------------- customerGroupID pk customerGroupName customerGroupDescription CGDiscountPCT CGDiscountStartDate CGDiscountExpireDate customerGroupSponserID fk from userID books/cutomerGroups -------------- bookID customerGroupID books -------------- bookID pk bookTitle bookSubtitle bookDescription bookImagefile publisherId fk publishDate pageVisits --------- pageVisitID bookID userID IP brouser visitDate
Bookmarks