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 |
|
leezo
Starting Member
9 Posts |
 Posted - 2008-06-04 : 10:18:16
|
| Hi,I am a bit confused and wish to share this with you for help. We are designing a billing application to bill telephone calls. It currently handles a single rate plan. So what it does is that it looks up the RATES table and matches the called number area code with the RATES.ACCESS_Code field to find the tariff for that area and multiplies that by the number of minutes. Here is the current schema.CALLS• ID (pkid)• Called Number• DurationRATES• Destination Name• Access_Code (pkid)• TariffNow the problem is that we need to process calls based on RATES per OPERATOR. Each operator is a telephony carrier with similar RATES. However, each call will be prefixed with a number to indicate which operator carried that call. Accordingly, the database should relate that prefix with the proper operator and then looks up the RATES that are related to that operator.In conclusion we will have a replica of the RATES table for multiple operators. An operator is only supposed to have two fields I guess (name and ID).So now we need to re-engineer the schema to adapt to this situation. Eg. 95004433313445 (Will be identified as BT operator) 93004422376234 (Will be identified as AT&T operator)Can anyone help please?PS: we are using SQL Express 2005Thanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-06-04 : 10:22:20
|
| How will you relate RATES table with OPERATOR?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
leezo
Starting Member
9 Posts |
Posted - 2008-06-04 : 10:34:25
|
| That's why I am kind of lost. I can't think of a way that helps in the context I am talking about. Well, normally this relation can be using a foreign key. Operator ID will be linked to Operator ID (fk) in the Rates table. Howver, if I attempt to do that, this will mess up the RATES pkid because it is supposed to be unique and I would want to add the same country access code for the second operator with the new rate. So can you see where my problem is coming from!! |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-06-04 : 10:40:36
|
| Why don't you split up Rates table like this?Rates-----Access_Code (pk)Destination NameOperatorRate-----Access_Code (fk from rates)OperatorID (fk from operator)TariffHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
leezo
Starting Member
9 Posts |
Posted - 2008-06-05 : 07:49:53
|
| Hi,Thanks for your response. I have thought of this already but then it will create a problem. In the NewRates table, the field (Access_Code) which is a (pkid) is supposed to be unique. However, I need to enter the tariff for the same access code on per operator basis. Meaning I will need to have two (001) - as USA for both operator1 and operator2. Did you get what I mean?!I liked this schema because it is making my life easy when querying the databse.Let me know your thoughts.Thanks |
|
|
|
|
|
|
Table Schema problem!!