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