Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    How does my DB look? Did I design it right?

    I created a DB that will be used for a website that will sell downloadable products.

    People will be able to register on the site without having bought anything, that is why it isn't mandatory to have a credit card or purchase entry per user.

    I haven't ever designed a DB before and I really don't know what I'm doing. But does this look right?

    I attached an image of my DB layout.
    Attached Thumbnails Attached Thumbnails db2.gif  

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Can two users have the same credit card? If yes, you may have to make a slight change to the data model to avoid duplicate credit-card information.

    Ravi

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    One other thing. There is no need to have a product_basket_id. Your model should work even after you remove this column from both the tables.

    Also, the purchases table should have only purchase_id as its primary key, while a combination of user_id, credit_card_id, purchase_timestamp should be declared unique. Of course, the column to capture the purchase_timestamp is there to let one user make more than one purchase using the same credit-card.

    As shown in the original post, your model assumes that there is only one product_basket with exactly one product for each purchase. Is that a business requirement?

    Ravi

  4. #4
    Join Date
    Jan 2004
    Posts
    11
    Two users can't have the same credit card. But one user can have multiple credit cards.

    What would I have to change?

  5. #5
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Your model can have a user with many credit cards.

    Ravi

  6. #6
    Join Date
    Jan 2004
    Posts
    11
    Quote Originally Posted by rajiravi
    One other thing. There is no need to have a product_basket_id. Your model should work even after you remove this column from both the tables.
    I removed this...

    Quote Originally Posted by rajiravi
    Also, the purchases table should have only purchase_id as its primary key, while a combination of user_id, credit_card_id, purchase_timestamp should be declared unique. Of course, the column to capture the purchase_timestamp is there to let one user make more than one purchase using the same credit-card.
    If a user buys twice with the same credit card or username, wouldn't either a duplicate user_id or credit_card_id cause an error? Or does the unique declaration mean that (user_id AND credit_card_id AND purchase_timestamp) have to be unique?

    In what table does user_id, credit_card_id, and purchase_timestamp need to be unique?


    Quote Originally Posted by rajiravi
    As shown in the original post, your model assumes that there is only one product_basket with exactly one product for each purchase. Is that a business requirement?
    What I intend is that a person can have multiple products in their order. And have multiple orders if they continue shopping multiple times at the site.

    Should I even have a product_basket category?

    I attached my updated design:
    Attached Thumbnails Attached Thumbnails db3.gif  

  7. #7
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Or does the unique declaration mean that (user_id AND credit_card_id AND purchase_timestamp) have to be unique?
    That is correct. In purchases table. You store the information of the user who made the purchase, what credit-card was used, and when the purchanse was made.

    You definitely need the product_basket table to know what products were purchased in any given purchase/order.

    Ravi

  8. #8
    Join Date
    Jan 2004
    Posts
    11
    First I should state some of the objectives of the DB.

    1) People will be able to register to the site with just an email and password, no purchase is required.

    2) People can have more than one credit card under their account (this won't be a feature but I still want to learn how to implement it).

    3) People can purchase multiple downloadable software items at one time, they will be able to do this multiple times. eg. buy two downloads Tuesday and three on Friday.

    4) They can buy the software auth codes and it stores the subscription length of the auth code, price, and date purchased with the auth code.

    5) After they purchase the item, a SQL script will show them their purchased items and auth codes codes.

    I changed the design of the DB, how does it look now?
    Attached Thumbnails Attached Thumbnails db4.gif  

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    In purchases table, purchase_id should be primary key. Additionally, there should be a unique key on a combination of some other columns such as (user_id, credit_card_id, timestamp)

    The product_basket table should have a combination of purchase_id and product_id as primary key.

    There is no need to carry the purchase_id column into the products table.

    Does each product have more than one authorization_code? Is a user assigned a distinct auth_code for each product? In other words, if two users buy the same software, then can they be assigned the same auth_codes or will they have different auth_codes?

    Ravi

  10. #10
    Join Date
    Jan 2004
    Posts
    11
    Quote Originally Posted by rajiravi
    Does each product have more than one authorization_code? Is a user assigned a distinct auth_code for each product? In other words, if two users buy the same software, then can they be assigned the same auth_codes or will they have different auth_codes?

    Ravi
    Each product can have more than one authorization_code. A user is assigned a unique authorization_code for each product. If two users bought the same software, they will have different authorization_codes.

    Here is my new DB with some modifications. How does this look? There will be a unique key on purchases.USER_ID, purchases.CREDIT_CARD_ID, and authorization_codes.date_purchased.

    How does it look? Thanks again for your help!
    Attached Thumbnails Attached Thumbnails db6.gif  

  11. #11
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Here's how I would design it based on your requirements:


    awohld wrote:
    First I should state some of the objectives of the DB.

    1) People will be able to register to the site with just an email and password, no purchase is required.

    2) People can have more than one credit card under their account (this won't be a feature but I still want to learn how to implement it).

    3) People can purchase multiple downloadable software items at one time, they will be able to do this multiple times. eg. buy two downloads Tuesday and three on Friday.

    4) They can buy the software auth codes and it stores the subscription length of the auth code, price, and date purchased with the auth code.

    5) After they purchase the item, a SQL script will show them their purchased items and auth codes codes.
    In the following, PK stands for Primary Key, FK for foreign Key, and U for unique constraint.

    Users(User_Id (Pk), email, ...)
    CreditCards(Credit_Card_Id(PK), cardNumber (u), ....)
    UserCards(User_id(FK,PK), credit_card_id(FK,PK), ...)
    Purchases(purchase_id (pk), user_id, (fk, u), credit_card_id (fk,u), purchase_dt(u), ....)
    Products(product_id (PK), name, ...)
    Product_Basket(purchase_id (pk, fk), product_id(pk,fk), auth_code_id(fk) ...)
    Authorization_Codes(auth_code_id (pk), subscription_length, ...)
    That should be abl to handle all the requirements that you specified.

    You may choose to include the user_id column in creditCard table and not create the user_cards table.
    Strictly speaking, you do not need a credit_card_id, the cardNumber should suffice. But then, that is a minor issue.
    One of the keys to design is to create focussed tables (entities) that deal with just one concept.

    For example, authorization_code should not have the purchase_id or the user_id. It should only have columns that have information relevant to authorization_codes. And credit_card table should only store information about credit_cards, not about users.

    Although it sometimes seems like overkill, it is a very small price to pay for the flexibilty that you get when you are asked to handle new requirements.

    Hope that helps.

    Ravi

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ravi, you might be interested in this thread on another site to see a different version of this same data model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Rudy,

    It is interesting that people ask the same questions in two (or more) different forums.
    The end result is that they will confuse themselves when they get two different answers from two people. Won't know which one to accept and will end up mixing the two.

    I am strongly tempted never to answer a design/data model question except in the most general terms.

    Ravi

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i've run into it many times

    often, i will copy/paste the same answer into each forum just to get the poor guy straightened out

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by awohld
    I created a DB that will be used for a website that will sell downloadable products.

    People will be able to register on the site without having bought anything, that is why it isn't mandatory to have a credit card or purchase entry per user.

    I haven't ever designed a DB before and I really don't know what I'm doing. But does this look right?

    I attached an image of my DB layout.
    I'm not even looking at it because I've done roughly the same thing.

    You can't start with a schema and expect to design the site around it.

    What worked for me was to:

    1. Type up my requirements document. Look on the web for examples.

    2. Draw up a storyboard showing every single page, down to the littlest popup window that asks "are you sure?", in a flowchart fashion. This proves that you know all the steps the user will go through to actually make a purchase.

    3. Go back to step 1. Keep it up until you really are confident that you've adequately planned out what's going on. Find out what data your purchase handling requires. A good example can be found in the Business::OnlinePayment modules. Your users will require self help, as well.

    4. Once you've worked out all the inputs and outputs for every single page, you can determine what data is being passed via browser (FORM tags and URL variables) and what queries are being made.

    *Now* you can draw up that schema.

    BTW, look at existing open source e-commerce sites. Don't reinvent the wheel...

Posting Permissions

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