Should I Merge Service and Usage Charges

badabing1
badabing1 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Contract ERP Admin/Consultant
Commented:
I would definitely merge them. Add a column (whatever you desire to name it) that signifies what type of charge. That will make your query world much easier.

Now, if this were an industrial size multi-GB table, you could keep them split, but I'd line up the columns, and then for consolidation, create a view that UNIONs them both.

But if it's not too terribly big (less than 1-5GB for that one table), you should go with one table.
Top Expert 2011
Commented:
i think i'd keep them separate...

i would consider a union to perform the sum across the charge amounts...

however  surely your "service table" should actually have two parts...

1 a reference table for the possible set of charges for an "asset"
2 the service charge for the particular asset

1 would have the description and a set of dates relating to its application
   and category information , and/or something to identify a related asset
   class to which its applicable

2. each Asset needs its own service charge table...
    for the billing period...

?
     

Author

Commented:
Thanks both for the feedback, much appreciated.
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?
dsackerContract ERP Admin/Consultant
Commented:
For ease of querying in your OLAP, it would make sense. In fact, if you're setting up any type of star schema there, you'd probably want that as your fact table.

Author

Commented:
Thanks for the advice.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial