Database Schema Help needed!!

[wzb]

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
• Duration

RATES
• Destination Name
• Access_Code (pkid)
• Tariff

Now 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?

Thanks

[ithelp]

Add operator id in the table rate may be.

[tesuji]

Hi wzb,

. . .
CALLS
• ID (pkid)
• Called Number
• Duration

RATES
• Destination Name
• Access_Code (pkid)
• Tariff
. . .
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)
. . .

You can create new table Operators

OPERATORS
•	operatorID (pk)
•	name

Then add operatorID to RATES

newRATES
•	Destination Name
•	Access_Code (pkid)
•	operatorID (pk ??)
•	Tariff

I hope you can select the right operator for a given "Called Number".

Do not create separate RATES tables for various operators. All rates with identifying operator information should be in one table. If not, you would run into difficulties when creating SQL select statements.

krs,
tesu

[wzb]

Hi tesu,

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

[tesuji]

Hi wzb

Hi tesu,
... 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...

I also assumed this. It has been the reason why I wrote operatorID (pk ??) in newRates. So opeatorID must become part of pk. Hope this will work.

krs,
tesu

[wzb]

Hi wzb

I also assumed this. It has been the reason why I wrote operatorID (pk ??) in newRates. So opeatorID must become part of pk. Hope this will work.

krs,
tesu

I am sorry tesuji, I don't get what you mean. Could you please explain!

Thanks

[tesuji]

hi wzb
Primary key of newRates should consist of Access_Code and operatorID to allow that same access code can be assigned to various operators. Like in:

create table newRates(DestinationName varchar(10) not null, 
Access_Code varchar(15) not null, operatorID varchar(15) not null, Tariff decimal(10.4),
primary key (Access_code, operatorID), foreign key (operatorID) references operators);

Thus far, I have a problem with your data model: If a call is made, the information is recorded in the CALLS table. CALLS table has called number and duration to compute the total amount. To do this you need the tariff of the operator, e.g. from AT@T. what operated that specific call. Is this necessary information stored in CALLS, maybe together with called number?

krs,
tesu

[wzb]

Hi tesuji,

Yes you are right, I need to identify the operator before the actual call rating. I intend to do that by creating a query to match the prefix that the user dials before the actual number. I also intend to have this match the OperatorID. From a configuration perspective, I will give the user the option to configure the application with all the possible operators that they use it to operate calls along with their prefix which I will use as the operatorID. What do you think of this model? Is there a better alternative?

thanks
wzb