quote:

---

Originally posted by Peso

Sample data/expexted output and an explanation of what "exempt" is?


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by Michael Valentine Jones


What you are doing is basically an inventory system for keeping track of vacation hours inventory, so it has many of the same problems.

You mentioned the idea of a calculating a running total vs. just storing the amount. The problem with having only a running total is that it makes it difficult to purge old data, because you need all of it to be able to calculate the current amount. Also, calculating the total will take longer over time as the number of rows in the table increases. Just having a row in a table with the current balance is not very good either, because it is easy for it to get out of sync.

It is common to solve this problem by having a row that represents a balance at a point in time, like beginning of month, and then calculate the current balance by adding up the rows for the current period only. Then you can remove older transactions rows that are no longer needed. You can also have the start of period balances in a separate table; it is especially useful in inventory applications to be able to quickly look at balances at a point in time. A disadvantage of this method is that the start of period rows can use a lot more space in situations where you have fairly low transaction volume compared to the number of periods.

I have also seen the approach of calculating the balance after every transaction, and including that balance on each transaction row. One problem is that transactions do not always arrive in time sequence, so you may get an adjustment for something that happened weeks ago, and then you have to recalculate the balance on each individual transaction going forward from that point in time. Also, it makes it more difficult to do a balance at a point in time, unless you also insert dummy transactions at the start of periods to hold the balance at that point in time.

As for having one table vs. many tables, you might need to combine both approaches. Put the attributes that are common to all types of transactions in the main table: user id, transaction date, transaction type, etc. Enter attributes that are specific to a particular type of transaction in tables that are related to the main transaction table. This is especially good if you have individual transactions that have related data with complex relationships. For example, if someone sells a vacation day, someone else is buying a day, so you will need a sale row that to related to the two different transactions for different employees. This is similar to an inventory transaction that transfers products from a warehouse to a store.

Another thing you may want to consider is how this system will relate to the company’s financial systems. If it is producing transactions that will feed the financial systems, you will also have to allow for those requirements. For example, if it is necessary to adjust the vacation used for someone for last year, you may need to report this in a current financial period, because last years books are closed. You may need to record a separate financial reporting date for each transaction that could be different than the actual date of the transaction, and have clearly understood rules for this. You might also have to calculate the balance at a point in time differently for financial reporting purposes.

I hope this is helpful. I know I am not giving you any clear answers here; I’m only trying to bring up issues that I had to deal with. It’s easier to deal with these problems at the design phase than after implementing a system.



CODO ERGO SUM

---

quote:

---

Originally posted by jsmith8858

Jon -- I agree with your approach and what MVJ says as well. Keep it as a large transactional table with the different transaction types and simply aggregate as necessary. Running totals can be displayed on a report or webpage as necessary very easily, much easier than doing in T-SQL; the most important thing that your code will be concerned with will be grand totals, not running totals, and we all know that is very easy to aggregate off of a transactional table.

I also agree that storing rates and types and min/max years and all that as much as possible in tables is the way to go. Looks like you are going in the right direction.

It's tough to explain to a co-worker why a design that might seem more complicated at first is actually much more flexible and even simpler than the "one big table that we update" approach that they are used to, hopefully you will have success in educating them.

I did a similar application that was a full-blown timesheet, with certain project codes that correlated to vacation, unpaid time off, sick time, etc. What you have described is basically how I did it (though this was back in 1999 and I used a combination of Excel and Access since that's all I had to work with at the time! you should have seen this beauty .. though the schema was pretty well designed,I thought, and it did work quite well for about 4-5 years).

You can also keep benefit accrual transactions in 1 table and the actual time taken in another table if you like and always UNION ALL the two transactional tables together as well.


- Jeff
http://weblogs.sqlteam.com/JeffS

---