Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesDatabase Management

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old September 18th, 2003, 07:09 PM
Cira9999 Cira9999 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2002
Posts: 226 Cira9999 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 38 m 41 sec
Reputation Power: 7
db schema question

I'm building a product catalog database with MySQL and I'm wondering if there is a more efficent way to create my tables.
Here's the structure:

product
----------
product_id
product_name
product_desc

size
-----
size_id
size

color
-----
color_id
color

price
-----
price_id
price

quantity
-----
quantity_id
quantity

map_price
-------------
map_price_id
product_id
price_id
quantity_id

map_size
------------
map_size_id
product_id
size_id

map_color
---------------
map_color_id
product_id
color_id

There are obviously more tables and more information in the db, but this all that is required to answer my question.

So the relationship is, one product can have many sizes and colors, it is possible for a product to have zero sizes and colors. Size and color are not related.

One product can also have up to 5 prices and quantities, price and quantity are related so that a quantity of 100 is $4.00 while a quantity of 500 is $3.80. However, this does not hold true for every product. For example, another product with a quantity of 500 might be $6.00.

In this application, quantity is based on price, the users are buying the products in bulk, so for every x increase in quantity, the price will go down. Unfortunately there is no pattern between the price / quantity relationship, so I can't apply an equation to figure the pricing.

I have set up mapping tables that relate the color, size, price and quantity to one product. My question is: is there a way that is just as efficent to minimize the number of mapping tables? Since size and color are not related, I can't think of a way to combine those tables. Perhaps not in this schema, but definitely in others I end up with about 5-6 mapping tables and 5-6 two-field tables like the color and size table above. This seems like a flawed design, although the data is normalized, I end up with a lot of tables.
__________________
-Samantha

Last edited by Cira9999 : September 18th, 2003 at 07:12 PM.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesDatabase Management > db schema question


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


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

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway