SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot akohl's Avatar
    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

  2. #2
    Sultan of Ping jofa's Avatar
    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
    ...

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    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
    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:


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

    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

  4. #4
    SitePoint Zealot akohl's Avatar
    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.

  5. #5
    SitePoint Zealot akohl's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •