Results 1 to 5 of 5

Thread: DB schema...

  1. #1
    Join Date
    Jun 2004
    Posts
    9

    Unanswered: DB schema...

    Hi all,

    I want to set up a db for an e-commerce site. I need to know how to set up the db correctly with out getting anything mixed up ... 'cos I never done an e-commerce db b4:

    I need to know where and how to store the passwords, the product pictures and the customers delivery address which is different from the billing address.

    The tables are as follows...

    --Customer--
    CustID
    Email (email & pwd is for login)
    password (will this be secure here)
    Name
    Address (should it all be in one column or firstline,secondline,zipcode columns)
    DeliveryAddress

    --Product--
    ProductID
    ItemName
    Catogary
    Price
    SellingPrice
    Quantity
    ItemsPicture...(not sure where to link the pics to)
    DistributorsID (the warehouse who dispatches the item)

    --Distributors--
    DistributorsID
    Name
    Company
    Address

    It would be much appreciated if you could share some info and tips for how to set it up all correctly.

    Tools I'll be using are, ASP.NET(C#), ADO.NET, MSSQL (Stored procedures)

    Thanks In Advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Store the password in the customer table, unless you intend to keep a history of passwords for aging and expiration. In that case, store it in a child table off of customer.

    If you are only storing a maximum of two addresses per customer, then store both in the customer record. It is not worth the hassle to normalize in case like this.

    If there are multiple pictures per product, store them in a related table. If there is only one, then store it in the product table.

    Be aware that many knowledgeable people on this forum recommend NOT storing the actual image in the database, but instead storing the file path of the image.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Posts
    9
    Thanks for those tips...

    I've looked into storing images via links to folders...also is there any other way to put the links in other than coding it (i.e. using the GUI interface)

    As for the passward, how do I encrypt it... 'cos I do not want ppl who have access to the db to view them.

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    1. Images....normally you just have one links table that defines the directory for each type of file. You can the store the directory_id and the name of the image in the table that houses image information.

    2. You really need to encrypt the password in your application. Look at asp.net for an example including code. The aspalliance.com website also has an example.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a one-way encryption algorithm you may use. When you user creates a password, pass it to this function and store the result in your password table.

    When they log in again and enter their password, pass it to this function again and compare the result to what is stored in their password record.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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