If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Kinda Confused on Relationships for my database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-01-04, 16:18
artisticre artisticre is offline
Registered User
 
Join Date: Oct 2004
Posts: 1
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.

Product
ProductID
prod_name
prod_desc
ImageID
CategoryID
OptionID
PriceID
SizeID

Categories
CategoryID
Category

ImageID
ImageID
image
th_image

Options
OptionID
options

Price
PriceID
Price

Size
SizeID
Size
PriceID
Reply With Quote
  #2 (permalink)  
Old 10-03-04, 18:54
yellowmarker yellowmarker is offline
Registered User
 
Join Date: Jul 2004
Location: Dundee, Scotland
Posts: 107
I'd suggest the following:

PRODUCT table
product_id
category_id
prod_name
prod_desc
large_image
thumbnail_image
(show/hide indicator)

notes:
- 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.

CATEGORY table
category_id
category

OPTION table
option_id
option

notes:
- used for tshirt colour

SIZE table
size_id
size
price_id

notes:
- 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

PRICE table
price_id
price

ORDER table
order_id
order_datetime
customer_id
price_grand_total
date_payment_received
date_goods_despatched

notes:
- the order table is important since it would capture customer transactions

ORDER_DETAIL table
order_detail_id
order_id
product_id
option_id
size_id
quantity
price_sub_total

notes:
- 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

CUSTOMER table
customer_id
customer_first_name
customer_last_name
customer_email
customer_address_line1
(etc.)

notes:
- this CUSTOMER table enables you to hold their address & contact details in one place.
- this would also be of use in terms of sending occassional promotion offers to past customers
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On