Create a table for prices
-
Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price). After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city. my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice Table order OrderID, CostomerID,... The problem with prices : there are many prices for same product when they ordered (each city has a different price)
-
Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price). After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city. my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice Table order OrderID, CostomerID,... The problem with prices : there are many prices for same product when they ordered (each city has a different price)
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done: Have a table called Products: ProductID, ProductCode, ProductName,... Have a table called Cities: CityID, City Name Have a table called Prices: PriceID, Price Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID Pro: This way your data remains in a normalized form which is considered to be a good design approach. Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that. Hope it helped. :)
-
Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price). After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city. my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice Table order OrderID, CostomerID,... The problem with prices : there are many prices for same product when they ordered (each city has a different price)
Based on what you say, it sounds like there is one price per city. If so, I will differ a little from above answer. I would have designed as: Have a table called MstProducts: ProductID, ProductCode, ProductName, CityID, ... Have a table called MstCities: CityID, City Name, Price This covers 'each city has different price' and is also normalized.
Sandeep Mewara Microsoft ASP.NET MVP 2012 & 2013 [My Blog]: Sandeep Mewara's Tech Journal! [My Latest Article]: HTML5 Quick Start Web Application
-
Well, there are multiple ways to solve a problem. It depends on whether you want to achieve speed or whether you want to have a normalized database. Here's what I would have done: Have a table called Products: ProductID, ProductCode, ProductName,... Have a table called Cities: CityID, City Name Have a table called Prices: PriceID, Price Have a table for mapping products with prices and cities, ProductPrice: ProductID, CityID, PriceID Pro: This way your data remains in a normalized form which is considered to be a good design approach. Con: You have to perform multiple joins while fetching data which will hamper performance a bit. But, I don't think you have millions of cities. So, I won't be bothered much with that. Hope it helped. :)
Maintain normalized database whereas you can avoid redundancy and if you are not maintaining more cities for one city then you can go for same table.
-
Hi, I wanna ask if I can put just one table for both prices an cities. In the beginning I created just table Product, but I have two different prices by cities(each city has a particular price). After that I put two tables one for cities and one prices. but I think I don't need table cities, I'm asking if I can create one table prices with field city. my data base NOW contains : Table Products: ProductID,ProductCode,ProductName,CityID,SubCategoryID... Table Cities: CityID,PriceID,City Name. Table Prices : PriceID,Price Table OrderDetails: OrderDetailsID, OrderID, FK ProductID, UnitPrice Table order OrderID, CostomerID,... The problem with prices : there are many prices for same product when they ordered (each city has a different price)