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 |
|
ngm
Starting Member
3 Posts |
 Posted - 2002-01-23 : 07:11:26
|
| Hello there,I'm having huge problem with one table relationship design in my database.I have following tables which acts in this problem...1.product_list (table name)pr_id (pk)ct_id (fk) - foreign key from category tableother fields2.category (table name)ct_id (pk)other fields3.clients (table name)cl_id (pk)other fields4.shops (table name)sh_id (pk)other fieldsSo here's the problematical one:5.product_price (table name)pp_id (pk)ct_id (fk) - foreign key from category tablecl_id (fk) - foreign key from clients tablesh_id (fk) = foreign key from shops tableIt means that product price will depend of category, clients and shops. Price doesn't depend of product, it depends of category instead. Every shop has its own price for every single category, but some clients can have some different prices for particular category but not for all of them. It means that client Mr. XXX can have totally different price for first two categories but not for all others. Also the clients who have not definied price for some category, have normal or standard price which is valid for that category and that shop. Only if price is specified for specific client, shop and category then it's valid.I believe you can already see the problem, you must set price for all categories and all combinations with shops and clients even if client has got standard price. The base has got more than 5,000 clients and 500 categories. When you apply new client, shop or category you'll have to set all those combinations again or SQL statement won't return the price because there are three relations.Wow, this was an essay? 8))Any suggestions in redesigning database? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-23 : 13:10:23
|
| I think the structure you have now is OK. I need to understand the relationship between a client and a shop though (future reference-please include ALL of your columns when you provide table structures!) 1. Can a client and category combination be considered unique? In other words, it's the same regardless of which shop is involved? 2. Or can a client have different pricing in the same category, but different shops? Also, can every client be related to every shop, or only certain shops? This is pretty important, because you will definitely need another table to relate clients and shops, unless the client/category relationship is the only factor for pricing (#1).You can try two methods to handle this. One is to fully populate the product_price table (#5 in your post) with all of the valid combinations, then UPDATE the appropriate rows to match the client/product specific pricing. I think this is the best way to do it, but you need to maintain this table each time a new client, product, or category is added. You could set up triggers to do this fairly easily.The second method would require that the product_price table ONLY store exceptions to standard category pricing. You'd have to write queries to LEFT JOIN to this table and test for an exception pricing, and to substitute it instead. This requires less table maintenance, but the query is tricky and performance will almost certainly be worse than keeping all pricing in the table beforehand...especially if #2 from above applies. |
 |
|
|
ngm
Starting Member
3 Posts |
Posted - 2002-01-23 : 14:52:53
|
Hi Rob,quote:
I think the structure you have now is OK. I need to understand the relationship between a client and a shop though (future reference-please include ALL of your columns when you provide table structures!) 1. Can a client and category combination be considered unique? In other words, it's the same regardless of which shop is involved?
No, it can't be considered unique.quote:
2. Or can a client have different pricing in the same category, but different shops?
Unfortunately, YES... client can have different price for the same category in different shop.quote:
Also, can every client be related to every shop, or only certain shops? This is pretty important, because you will definitely need another table to relate clients and shops, unless the client/category relationship is the only factor for pricing (#1).
All clients have to be available in all shops. But if price for some client isn't defined in one of shops the standard price is used. To specify what's standard price, it's regular price that appears in every single shop and it depends of category (ct_id) and shops (sh_id), so that combination of (ct_id) and (sh_id) must be presented in table product_price for sure. The problem is client (cl_id) side because as I said, not all clients have got different prices for all categories in all shops. Just let's say Mr. XXX has got price other than standard one for category (ct_id=200) in shop (sh_id=3). What about other categories in other shops for Mr. XXX or for other clients who haven't different prices?quote:
You can try two methods to handle this. One is to fully populate the product_price table (#5 in your post) with all of the valid combinations, then UPDATE the appropriate rows to match the client/product specific pricing. I think this is the best way to do it, but you need to maintain this table each time a new client, product, or category is added. You could set up triggers to do this fairly easily.
Well, I don't think it's the best and only solution since there are really too many combination and when user enters new category record (what happens offten) thousands of records have to be added in product_price table.quote:
The second method would require that the product_price table ONLY store exceptions to standard category pricing. You'd have to write queries to LEFT JOIN to this table and test for an exception pricing, and to substitute it instead. This requires less table maintenance, but the query is tricky and performance will almost certainly be worse than keeping all pricing in the table beforehand...especially if #2 from above applies.
Hummm... I was thinking that solution can be something like this, but still don't know how to complete the SQL statement:... JOIN (product_price.cl_id = clients.cl_id AND product_price.ct_id = category.ct_id) OR produuct_price.ct_id = category.ct_id WHERE product_price.sh_id=2It means that product_price, clients and category will be joined through cl_id and ct_id but if there's no match it has to join ONLY product_price and category on ct_id.This SQL command above will show both conditions because OR operator.Anyway, thank you Rob for your time.Cheers! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-23 : 15:41:54
|
| Well, the other reason why I think full-population is better is because you'd only need to query one table, and if it's properly indexed it will be pretty zippy even with 100,000+ rows in it. The second method will involve at least three tables, and will hold locks and other resources on them for the duration (you could use NOLOCK hints on the SELECT statement, but it could cause phantom row problems...very minor and unlikely though)I'm suspicious about the JOIN statement you provided, because the 2nd part (after the OR) could very easily return rows you don't want. You'll end up getting BOTH client specific pricing and category pricing. Unfortunately I can't figure out a SELECT statement for it without the rest of the table structures and the columns you wish to see in the result. If you post everything I'll take a crack at it. |
|
|
|
|
|
|
|
|
Table Relationship Design