please comment on this db design

[akohl]

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.

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.

shippingMethods
---------------
shippingMethodID
shippingMethodName


shippingScales (junction table)
--------------
shippingScaleID
shippingMethodID
bookWeightCodeID
shippingPriceFirst
shippingPriceAdditional
additionalStartAfter


bookWeightCodes
-----------------
bookWeightCodeID
bookWeightCodeIDName
beightMin
weightMax

[jofa]

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

[DaveMaxwell]

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:

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

Now, Jofa had a good idea that you will want to keep track of orders, so let's define a status table.
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:

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

OK, that takes care of the order itself. Now let's look at the shipment portion of the process. You'll really
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:

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

[akohl]

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.

[akohl]

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!

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