Page 1 of 1
Dynamic vs Static coding against db stored data question
Posted: Mon Apr 12, 2010 11:47 am
by basudz
Hello, I have a question pertaining to the very ugly system that I work with daily. I'm a junior level developer and am looking for advice about how to design my code in certain situations that I come across.
The system I work with is a back office system for managing benefits. The project I'm currently working on revolves around service codes that are entered in during the data entry process, and these service codes are tied to paycodes which hold the payout percentages. Now there's over 1000 service codes in the system and the project that I've just got is that I need the system to add a static amount to the customers bill if one of five specific service codes is entered.
So the quick and dirty way would just be to hard code in checks for these specific service codes and do my processing from there. I'm wondering if there's a better / more dynamic way to go about this. It doesn't seem practical to add another field as a flag to the service code table just to satisfy 5 records out of 1000 or more.
Is there any other way I can go about this or is it not such a big deal to keep adding tinyint flags to my tables?
Thanks for the help!
Re: Dynamic vs Static coding against db stored data question
Posted: Mon Apr 12, 2010 12:16 pm
by JakeJ
Keep it dynamic.
You didn't give table names so I'm just going to make stuff up, adapt to your needs. Create a table as follows:
priceadj
---------
id
code_id (the primary key from your table with the codes. I would assume it's the code number itself)
amt (the amount you would like to add or a multiplier if that's appropriate)
description (if necessary)
Enter your adjustments in to the table above along with the code you need to adjust. Use a join query with priceadj.amt WHERE code_id == codes.id (assuming that your table with the codes is named code and the primary field is named id).
Lots of reference material on JOIN queries if you've never done it before.
This keeps your whole system flexible because if more adjustments get added in, you won't have to keep adding fields which breaks normalization rules anyway.
Re: Dynamic vs Static coding against db stored data question
Posted: Mon Apr 12, 2010 12:40 pm
by basudz
JakeJ wrote:Keep it dynamic.
You didn't give table names so I'm just going to make stuff up, adapt to your needs. Create a table as follows:
This keeps your whole system flexible because if more adjustments get added in, you won't have to keep adding fields which breaks normalization rules anyway.
Hey Jake,
Thanks for the info, you are correct in assuming the table structure is standard as far as the id's go.
I did think about goin this route too but this also poses a question that seemed a little impractical. I need to check each claim being entered by the user for these 5 specific codes, then I would have to run a query comparing the priceadj.code_id to the object property holding the service code. Since these are rare cases which these certain codes are actually entered (about 10 records per month out of 300-500 average) that seems a bit excessive on the system. Am I overlooking another way to go about this perhaps?
I'm quite comfortable with joins however I'm not real familiar with the standard rules of normalization which might be adding to my questions here.
Re: Dynamic vs Static coding against db stored data question
Posted: Mon Apr 12, 2010 3:04 pm
by pickle
Of course it depends on your situation, but I'd consider putting these in an array in a config file. Still pretty easy to update. It'll save you needing to put another JOIN in your query which, depending on your DB structure & size, could really add time to the query.
Re: Dynamic vs Static coding against db stored data question
Posted: Mon Apr 12, 2010 5:50 pm
by JakeJ
Why would it require another join? Just one should do it. Then an If statement to test if amt is empty. If not, add amt to cost. It would be a very simple routine and not memory intensive at all. Most of the records would be empty and it would move right along. Heck, it could even be optimized and sorted for non empty, count those fields and only run the loop $x times if there was really a concern.
Then again, his query could be structured to simply add amt to cost right in the select statement. You could do it with one query but it would be really optimzed with two. For just one query, do a join on the other table. But what you'll do with your select statment is create another field dynamically. "SELECT codes.cost + priceadj.amt AS cost FROM codes, priceadj WHERE <your conditions>"
If you really wanted to optimize it, make two queries, one to test only for matches between two two tables where you would do the addition and the other one would exclude the matches, then you could append one to the other.
The great thing about databases is there's often time so many ways to approach a problem.
Re: Dynamic vs Static coding against db stored data question
Posted: Tue Apr 13, 2010 2:00 pm
by alex.barylski
The great thing about databases is there's often time so many ways to approach a problem.
That is software development in general., IMO. Some people love it, otehrs hate it. I'm in the former myself.

Re: Dynamic vs Static coding against db stored data question
Posted: Tue Apr 13, 2010 6:09 pm
by Christopher
basudz wrote:The system I work with is a back office system for managing benefits. The project I'm currently working on revolves around service codes that are entered in during the data entry process, and these service codes are tied to paycodes which hold the payout percentages. Now there's over 1000 service codes in the system and the project that I've just got is that I need the system to add a static amount to the customers bill if one of five specific service codes is entered.
I don't think it matters whether it is 5 or 500, you should solve the problem properly.
basudz wrote:So the quick and dirty way would just be to hard code in checks for these specific service codes and do my processing from there. I'm wondering if there's a better / more dynamic way to go about this. It doesn't seem practical to add another field as a flag to the service code table just to satisfy 5 records out of 1000 or more.
Is there any other way I can go about this or is it not such a big deal to keep adding tinyint flags to my tables?
I don't see why adding a field would make much of a difference in a table with 1000 service codes. Are you currently having performance problems? And I wouldn't add a flag, I would add the "static amount" to go with each service code. So what if it is zero for most.
One benefit from having Model objects is that you can hide this kind of implementation detail behind an interface. Sometimes I will solve a problem like this quickly with a little code (and a big comment). Then as things change I review the Model code and see whether changing the schema makes sense. But it can all be done transparently to the application.
Re: Dynamic vs Static coding against db stored data question
Posted: Wed Apr 14, 2010 9:32 am
by basudz
Christopher wrote:I don't see why adding a field would make much of a difference in a table with 1000 service codes. Are you currently having performance problems? And I wouldn't add a flag, I would add the "static amount" to go with each service code. So what if it is zero for most.
Well the system is pretty dirty and difficult to work with since it has been a work in progress over the past 5 years and I've only come in recently to help out. And the original developer is no longer here, and comments are scarce. There are performance issues all over the system due to the way things are layed out but that's many other issues for another days thread lol.
These codes don't have values or amounts tied to them directly, but rather they are tied to a higher level object which has an amount set in the form of a percentage. There is a table for these codes called service_codes, and the table for the actual objects called paycodes. There is also a table in between linking many service codes to one paycode.
So a user entering a claim record will enter in a service code of 99281. The code is then looked up and found that it is tied to a Medical paycode (object) which has a payout of property set to 80% and is applied to the bill amount.
The 5 codes in question still need to be attached to the Medical paycode for the 80% payout amount, but also need to apply a 75$ charge as well. I thought about making a new object and inheriting the Medical paycode object for the original properties but that was shut down because paycode name has to remain the same, and because there are multiple other clients in the system that use these paycodes (yes sadly, all in the same code and database. There is no individualized systems per client and all the customizations are thrown into one big crapload of a system).
Hopefully that gives a little bit better idea of the mess I've walked into...I do like the idea of creating an array in the config file since this will not be an ongoing thing for specific codes requiring unique mods to them.