March 6, 2013 at 10:52 pm
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,...
March 7, 2013 at 4:51 am
schon0008 (3/6/2013)
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,...
Having separate City table sounds more reasonable, however you can go without it if you really want. But then you should just have CityName in your "Prices" table.
You should expect some problems with such design, one of this would be misspelling city names in your Prices table which clean state would be harder to maintain...
So, don't be lazy - create dedicated Cities table (without PriceId column) and then link table between Cities & Prices which will have FK to City and Price!
March 7, 2013 at 7:36 pm
Thank you for your help SSCrazy.
March 7, 2013 at 8:35 pm
Conceptually, product prices are usually retained in Price Lists. In your case, the price list happens to be by city but what happens when you have different customers in the same city that you are offering different prices to?
Don't limit yourself by calling the table Cities (which on the surface by name seemingly has no relationship to products). Call your tables:
- Products
- ProductPriceLists
With the latter coded by a price list designator which may initially start out as a city name.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply