May 17, 2013 at 6:29 am
I have a database that contains 2 tables (sql server 2008):
LenderCommission
ID int
Commission decimal
CommissionTier
ID int
MinCommission decimal
MaxCommission decimal
I want to create a relationship between the 2 tables. The obvious way would be to add CommissionTierId to the LenderCommission table, however this is then fixed. If the commission structure of a tier changed all LenderCommissions would need to be revaluated.
I'm no expert but I'm guessing that it's not possible to create a relationship where Commission is between MinCommission and MaxCommission. However, is there any way in which this relationship can be made more fluid?
May 17, 2013 at 7:07 am
webbies (5/17/2013)
I have a database that contains 2 tables (sql server 2008):
LenderCommission
ID int
Commission decimal
CommissionTier
ID int
MinCommission decimal
MaxCommission decimal
I want to create a relationship between the 2 tables. The obvious way would be to add CommissionTierId to the LenderCommission table, however this is then fixed. If the commission structure of a tier changed all LenderCommissions would need to be revaluated.
I'm no expert but I'm guessing that it's not possible to create a relationship where Commission is between MinCommission and MaxCommission. However, is there any way in which this relationship can be made more fluid?
For starters, neither of these entities provide enough information to make any type of judgement, let alone provide any direction.
Does "LenderCommission" define the commission rate for a lender? if so, how does it define which lender the commission may be associated with?
Is "CommissionTier" a definition of the possible minimum and maximum commission rates for something? If so, then for what?
It appears that "CommissionTier" is an attribute of "LenderCommission".
How about descriing the business rules?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 17, 2013 at 11:17 am
You could simply do the BETWEEN operation in your JOIN criteria within T-SQL, but you're going to have to be careful about your indexing. And, it's not an enforceable join (that means no foreign key constraint) which reduces some of the options that the optimizer can use. But you can do the JOIN that way within the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2013 at 4:11 am
u can use between while joining as said in above post but then it will not be optimized solution as ur query performance will hamper.
May 22, 2013 at 4:17 am
subhajeetsur (5/22/2013)
u can use between while joining as said in above post but then it will not be optimized solution as ur query performance will hamper.
Do you have any other better suggestions?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 22, 2013 at 5:13 am
subhajeetsur (5/22/2013)
u can use between while joining as said in above post but then it will not be optimized solution as ur query performance will hamper.
In what way? A BETWEEN operation can be indexed, so you won't get scans (if you write query correctly and create the right index and maintain the statistics).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2013 at 10:15 am
webbies (5/17/2013)
I want to create a relationship between the 2 tables. The obvious way would be to add CommissionTierId to the LenderCommission table, however this is then fixed. If the commission structure of a tier changed all LenderCommissions would need to be revaluated.
I can't imagine what a commission tier is for if not to place bounds on commissions that are in that tier, but clearly yours is for something else since if your business rules imply the conclusion I've quoted it can't be for that. But if it isn't for that, it is not at all clear why you want any sort of relationship between the two tables. And it seems very strange that a change to a single tier would require all commissions to be reevaluated, even those not related to that tier.
So I agree with
Michael L John (5/17/2013)
How about descriing the business rules?
Tom
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply