Avatar of badabing1
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.
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
badabing1

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
dsacker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Lowfatspread

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
badabing1

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
badabing1

ASKER
Thanks for the advice.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23