Unanswered: Kinda Confused on Relationships for my database
I am trying to add some things to my tshirt website
I have a product that has one category, two images, more than one option(color choices), more than one size and the prices is based off the size. Here are the tables I have created but I know this isn't correct. Can someone help me put the together the correct way. I am really confused so any help will be really appreciated.
- the thumbnail and the large image name would be held for each product (assuming you are going to store the image filenames and reference them from an image directory instead of trying to store them in the database
- a show/hide indicator would be useful to take products off the shelf so to speak without deleting them from the PRODUCT table
- I have moved the option (colour), price and size fields into the new ORDER_DETAIL table below.
- used for tshirt colour
- I have assumed that the price of a tshirt depends on the size, and that all tshirts are the same price... this approach would not be appropriate if some tshirts are more expensive than others...for example a more complete solution would be to hold a base price in the PRODUCT table for each tshirt which would enable some tshirts to be more expensive than others and then use the SIZE/PRICE tables to either apply discounts to smaller tshirts or increase the price of larger tshirts
- the order table is important since it would capture customer transactions
- an ORDER_DETAIL table is of use if a customer can purchase more than one type of tshirt or more than one of the same kind of tshirt
- it is useful to store the price with the other transaction information to 1) show sales history, and 2) enable you to change prices at any time without affecting historic sales transactions.
- always record the date/time when orders are placed since a small percentage of customers are bound ask questions about their orders