Need a suggestion on database design!

Need a suggestion on database design!

Hi all I am designing an online shopping website for a company...

While I was designing the database for the same I thought that I should discuss it's design first...

Well can you people help me getting started!

Welcome to the forum!

That's a very general field, there are a million ways to set up something like that. As a rough outline, why not start with a product table, indexed by a numeric ID column which also serves as the product ID. This table should have columns for price, category of product, name, description, thumbnails and any further illustrations.

Code:

ProductID Category Name Description Price ThumbnailImage Etcetera

Next you will want an order table, in which you save any and all orders being made. Start with an ID column (for the order itself for future reference in receipts, etc), and of course add one for timestamp as well. I would suggest you go with a log-in system for your customers, and if you do that you should have a column for the customer username or ID, but I would also have some columns for "manual" shipment recipient name, address etc so that your shop can allow the customers to make orders for others if they so please. You will also want a column in this table for the status of the order, which if you want you can make available to the customer on his customer page. As a last little suggestion, save the IP address the order was coming from, also.

Code:

OrderID Time CustomerID ShipmentName ShipmentStreet ShipmentZIP ShipmentCountry ShipmentPhoneContact IP Status

Now add a secondary order table where you can add product ID and amount of said items in a row along with the order ID. This set-up will allow your orders to be fully flexible in the amount of products and amounts they can contain.

Code:

OrderID ProductID Amount

Lastly add a customer table if you want to have a log-in system to help repeat customers shop more easily. Here I would suggest of course, username and password columns. Remember to allow the password column to have 32 characters of alphanumeric content, as you will want to MD5 encode any password input for security. You will also want columns for default shipment info, and perhaps registration date, e-mail (password resetting functions) and other misc. data.

Code:

CustomerID Username Password RegisterDate FirstName LastName Street ZIP Country Phone Email

As I said, it's a rough outline, there's tons of utility and complexity to tack on to it depending on your vision of the end shopping solution. Some ideas are cascading product categories, "wish lists", product availability info, and so on... We can probably all pitch in more input if you wish.