Trent Adams
asked on
Possible Additional Columns In SQL View To Support New Features
I have a very large project with a very tight deadline especially because of billable time. My client is wanting some payroll features to be added to the application (VB.NET/Web Forms). All the hours etc. are based on getting information from a view named vClaimLine. There are a few items on this list: An employee has a pay rate and event has a pay rate. An employee can have a pay override for an event. Meaning if an employee has an override in the EmpPlusEventOverride table then, this rate trumps their pay rate. So my question is am I able to add a column to the view using an additional join without screwing up anything? I know that seems like a loaded question but in theory anyway... This way in my code behind for a payroll report I could utilize that column to see if there is a value in it. If their is, then their pay will be set to either that value or that value times their hours.
I created three new tables, one to hold their base pay and one to hold an event rate and one to hold a combined set rate for the Employee Plus Event.
I created three new tables, one to hold their base pay and one to hold an event rate and one to hold a combined set rate for the Employee Plus Event.
Kevin Cross
In theory. In a perfect world this other table has exactly one row for each record in your normal employee table. Therefore, it just is an extra INNER JOIN and add your column. If only employees with an override exist in the other table, use an OUTER JOIN (I usually use LEFT OUTER JOIN or LEFT JOIN for short because as a preference I like to put the table I am getting all rows from on the left then add optional tables as joins after). Reading your question description, it sounds like you may have more than one override given a specific event code or date. If your view is detail by different event / dates anyway, then probably still good in theory but even if not you just need to summarize or pick one of the matches in the override table to use.
By the way, if the JOIN is easy, once you have both pieces of data in the view - there is nothing stopping you from doing COALESCE( override_pay, regular_pay, 0.00 ) as formula for the pay column, so your VB application just evaluates pay and doesn't have to get into the weeds of how you implement it on the back end so that if you change it later to be a multiplier versus and override, your app will not care. It just pays what the data says.
ASKER
That is what I was thinking. I am such a new engineer with not a lot of experience so looking through scopes is tough. I think in combination with my learning disability on top of being a novice. So, I find Experts-Exchange a good source to assist. I'm trying to learn everyday though. I appreciate your advice Kevin.
You are welcome! As another answer to your question, I was reading "without screwing up anything" as meaning the view code itself. No. However, if you have any previous applications that use SELECT * to bring back all the columns, they may not react well to have a different number of columns in the mix now. This would be another reason to perform the override and any other pay rate additions in the query, so the view returns one pay column (but more importantly the same number of columns it did before).
I hope that makes sense.
P.S. No worries. I am the same way. I find it tedious because as is the nature of business, the scope changes as soon as you understand it and usually right before you were about to implement it all.
I hope that makes sense.
P.S. No worries. I am the same way. I find it tedious because as is the nature of business, the scope changes as soon as you understand it and usually right before you were about to implement it all.
ASKER
Would elaborate on the statement: "This would be another reason to perform the override and any other pay rate additions in the query, so the view returns one pay column (but more importantly the same number of columns it did before)?"
Sure thing, noting that SELECT * is a bad practice but has scenarios where you may have not choice, so we are talking about something that may not exist in your environment.
Since you program in .NET, think about a grid that some programmer setup with 15 columns. In other words, an object you setup with a max capacity of 15. For ease of programming, you use a SELECT * FROM view_name, so you do not have to type in the 15 columns names. In turn, you loop through using for i = 1 to column count, then set the value of the element at i in your object to the value of the column at i in the SQL results. Now someone goes and changes the columns in the table to be 16. When the old program tries to run, you get an array out of bounds exception. This happens in the db. There are places where people INSERT INTO table_name SELECT * FROM view_name ... the target table would not know what to do with the extra column.
Hope those examples make sense.
Since you program in .NET, think about a grid that some programmer setup with 15 columns. In other words, an object you setup with a max capacity of 15. For ease of programming, you use a SELECT * FROM view_name, so you do not have to type in the 15 columns names. In turn, you loop through using for i = 1 to column count, then set the value of the element at i in your object to the value of the column at i in the SQL results. Now someone goes and changes the columns in the table to be 16. When the old program tries to run, you get an array out of bounds exception. This happens in the db. There are places where people INSERT INTO table_name SELECT * FROM view_name ... the target table would not know what to do with the extra column.
Hope those examples make sense.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.