Pricing calls

[villan60]

Hi

I need to be able to price calls made by phones. The way that I am doing it seems to go against design principles but I can't come up with a better way of doing it. I thought maybe someone here could point me in the right direction.

Each call record tells me what type of phone is making the call and to what type of phone so I have a table of OriginPhone, DestPhone, CallType, CallDesc. This allows me to get the call type. The problem is the cost of that type of call varies depending on the phone's features (dish size etc). The only way of knowing the phone's features is from my stored info, NOT from the call records. So I allocate each phone a special callplan. So the pricing table looks like this :

callplans
---------
PlanID (PK)
PlanCode
Desc
Indate
Outdate (bcos prices on a plan can change over time)
CallType1
CallType2
CallType3... (these contain the price for that call type on that call plan)

obviously it is not good to have the calltypes as fields because they vary and there are many.

any ideas?

Thanks
Dax

 

[Trendsetter]

Yes you are right - using your method you would need to put a new field in the table every time a new calltype was introduced..
use a one to many lookup table...
tblCallPlan:
PlanID
PlanCode
Desc
Indate
OutDate
CallTypeID

tblCallType:
CallTypeID
CallTypeName
CallTypeDesc
CallTypeRate

link the tables by CallTypeID and you can have as many types as you like and still be able to find the applicable rate for you calculations...

 

[villan60]

Thanks for the feedback...

I have studied your suggestion a bit, and I can't get it to work because the same calltype can apply to different plans but at different rates. But you have given me an alternative idea, something like this...

tblCallPlans
PlanID
PlanType
Plancode
Desc
Indate
Outdate

tblCallCosts
PlanID
CallType
Price

CtblCallsPerPlan
Plantype
CallType

This way I find the call plan active at date, find calltype for the planID and that gives me the price. The third table is for users to enter new call types for plans, because they will have to enter price changes.

What do you think?