Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adonweb
Starting Member
4 Posts |
 Posted - 2008-03-26 : 06:25:53
|
| I am creating a product table in which a single row displays complete information about a particular product such as productid, productname,price,image etc. But for some products , we have more than one price depending on their volume/sizes. How can i modify the table for more than one prices for a single product. I need to create a new table for prices. Please help. I am very new to RDBMS.. Sudden and favourable response will be appreciated. |
|
|
ranganath
Posting Yak Master
209 Posts |
Posted - 2008-03-26 : 06:48:17
|
| Hi, Create another Table for Prices columns should be ProductId, Price, Vol/size etc |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-03-26 : 07:08:39
|
quote:
Originally posted by ranganath
Hi, Create another Table for Prices columns should be ProductId, Price, Vol/size etc
1. Introduce a New column called "price id" in Product table2. Follow the steps posted by Ranganath3. Create a foreign key relationship between Price and Product tabel(Price.ProductId references Product.ProductId)4. Finally make sure you understand why we are doing this using Peso's postThanks! |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-26 : 07:13:58
|
quote:
Originally posted by pravin14u
quote:
Originally posted by ranganath
Hi, Create another Table for Prices columns should be ProductId, Price, Vol/size etc
1. Introduce a New column called "price id" in Product table
are you sure about that? 1 product would presumably have many prices, so introducing a priceID to the product table would mean a new row for every price / product combination, which is what we're trying to avoid, no?Em |
|
|
|
adonweb
Starting Member
4 Posts |
Posted - 2008-03-26 : 08:23:37
|
| I agree to elancaster. single product would have many prices. So create a column in product table wont a perfect solution. I hope the following idea may work.1.create a new table volumetable with productid and volume as composite key.2. volumetable.productid references product.productidWhat do you say? |
|
|
|
PABluesMan
Starting Member
26 Posts |
Posted - 2008-03-26 : 11:46:53
|
| Actually, I would say this could be a candidate for a many-to-many join. Now, before you break out the torches and pitchforks, hear me out ...If you go to Amazon.com and look under "MP3 players" you'll find approximately 15 bajillion different versions of the iPod Nano. They all have the same memory, capabilities, etc. ... and most importantly, PRICES ... but they are different colors, and as such have different product ID values.If this is similar to your situation, you may want to consider having the following tables (feel free to expand on them as needed):[Product]ID INT IDENTITY (1, 1)Product_ID VARCHAR (32) -- or however long your product code needs to beName VARCHAR (128)[Price]ID INT IDENTITY (1, 1)UnitPrice MONEY[ProductPrice]ID INT IDENTITY (1, 1)Product_ID INT NOT NULL -- foreign key to the [Product] tablePrice_ID INT NOT NULL -- foreign key to the [Price] tableThe advantage to this is that, if you have multiple product codes that are all of the same price, you can change that price in one place and have it reflected automatically through the product line. The disadvantage is that it becomes more complicated to change the price of a single product that is part of a multi-product line ... this requires entry or modification of rows in the [ProductPrice] table, and so on.It all depends on your business requirements. If your product line is such that each product is unique in terms of pricing structure, then this model won't help you at all and may make your life a wee bit more difficult. In that instance I would go with a much simple one-to-many model:[Product]ID INT IDENTITY (1, 1)Product_ID VARCHAR (32) -- or however long your product code needs to beName VARCHAR (128)[Price]ID INT IDENTITY (1, 1)Product_ID INT -- foreign key to the [Product] tableMinQuantity INT -- minimum number of units to qualify for this pricingMaxQuantity INT -- maximum number of units to qualify for this pricing. NULL or 0 (zero) would indicate that this was the top tier of the pricing structure.UnitPrice MONEYHope all this nonsense helps!I geek, therefore I am |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 12:21:48
|
| Can you give an example of a Product and it's different volume/sizes?It seems to me that this is not a simple modling issue, but a busines issue.If I have a Product of t-shirt with "I'm a geek" on it that is a product? What about sizes; S, M, L for example? Are those considered separate products? What about if it comes in mutiple colors; Red, White, Green, Black, etc? Or possibly, are you talking about a price discount based on the amount purchased? For example, if I buy 5-10 Shirts I get 10% off and if I buy 11+ I get 15% off? If that is the case then that is a completly different model.If you have some samples that would probably help us to offer a viable solution. |
|
|
|
pravin14u
Posting Yak Master
246 Posts |
Posted - 2008-03-26 : 12:22:31
|
quote:
Originally posted by elancaster
quote:
Originally posted by pravin14u
quote:
Originally posted by ranganath
Hi, Create another Table for Prices columns should be ProductId, Price, Vol/size etc
1. Introduce a New column called "price id" in Product table
are you sure about that? 1 product would presumably have many prices, so introducing a priceID to the product table would mean a new row for every price / product combination, which is what we're trying to avoid, no?Em
Sorry All,I have been stupid when I said this. I would go with the 2nd model suggested by PABluesManelancaster- Thanks a lot for pointing out my mistake. |
|
|
|
|
|
|
|
|
Database Design