badabing1
asked on
Should I Merge Service and Usage Charges
Hi all,
I am building a billing OLTP database to hold our provider charges. Currently I have set the database up so that the charges are split into two tables: Services table (for fixed charges) and Usage table (for call charges).
Because both of these tables share many columns such as; the asset it is allocated to, the division the charge is related to, the charge description, the date, the cost and the rerated cost, I am seriously considering merging the two into a charges table. I feel this would help as summing the cost column would be the correct bill total rather than having to add the service cost to the usage cost. The only problem I can see with this is that they do differ on some columns and the calls table is vastly larger than the services table. Also calls can be treated slightly differently in analysis and reporting. Nevertheless as long as these can be split out I cant see this being a problem.
Is merging these table the right thing to do at the OLTP level?
Thanks in advance for any help.
I am building a billing OLTP database to hold our provider charges. Currently I have set the database up so that the charges are split into two tables: Services table (for fixed charges) and Usage table (for call charges).
Because both of these tables share many columns such as; the asset it is allocated to, the division the charge is related to, the charge description, the date, the cost and the rerated cost, I am seriously considering merging the two into a charges table. I feel this would help as summing the cost column would be the correct bill total rather than having to add the service cost to the usage cost. The only problem I can see with this is that they do differ on some columns and the calls table is vastly larger than the services table. Also calls can be treated slightly differently in analysis and reporting. Nevertheless as long as these can be split out I cant see this being a problem.
Is merging these table the right thing to do at the OLTP level?
Thanks in advance for any help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the advice.
ASKER
It is likely that the 'Usage' table will be larger than 5GB, it could be 10 to 15GB in size so maybe it is better to keep them seperate. But what am I gaining by keeping them seperate?
I didnt want to go into too much detail but the service table does indeed have a reference table of all available charge types, this is shared with the Usage table. The asset table is joined to both the Service and Usage table by a one to many relationship, such that each asset can have multiple service charges and usage charges each billing cycle.
If I decide to keep them seperate at the OLTP level should I join them into a single charges table at the OLAP level?