July 28, 2004 at 7:10 am
Hi,
I've tried posting this request on the strategies forum but there have been no replies yet. Please forgive the duplicate post here.
I need to build a database to support an accounting type online transaction system. I've been working mostly with data warehouses recently and it's been a while since I've had to design a transaction system. The system needs to record several pieces of financial information across different time periods. Some types of transactions will have 12 monthly values, some 4 quarterly, some two half year values and a few will have a single value for the year.
I'm leaning toward a table that has an identifier for the type of time period such as M01 for month 1 or Q01 for quarter 1 and then a column for each piece of financial information. That would make the storage cleaner but will complicate the online part and the reporting when I need to list the individual time periods across the report. Everything needs to add up to a total year value on reports and screens regardless of the type of time periods in the detail.
Can anyone suggest a location where I can find books, design examples or best practices for this type of system. I hate to reinvent things and this should be a pretty common type of system.
Thanks in advance.
Don K.
July 29, 2004 at 2:33 am
I am reading "Pro SQL Server 2000 Database Design: Building Quality OLTP Databases" by Louis Davidson published by Apress (http://www.apress.com).
The ISBN is 1-59059-302-2. Check the Apress site as there maybe a later edition.
I have a fair amount of SQL experience and have not come across a better book for database design. It does a bit about normalisation like most books, but concentrates on the really awkward bits most books avoid. It's feet a well on the ground and the guy who wrote it has obviously had wide experience.
It looks the sort of things that are system wide which you really must get right from the start and discusses when different strategies should be used. The best bit (which is often missing) tells you how to calculate potential database size and use and how this affects the physical design/indexes/transactions/reporting.
My advice - think ahead, keep it simple.
July 30, 2004 at 7:30 am
Thanks Gary
I will look into ordering that book. I am going to keep it simple with each transaction stored separately. What I think I am looking for is a way to keep a running year to date total for each account as the monthly values are input. I'm also looking for a way to pivot the data using sql so that the individual transactions can be displayed as 4 quarters and a total.
July 30, 2004 at 8:27 am
In the same book their is a very detailed bank account reconcilation system which you'll be able to borrow loads of good ideas. One of which is...
Table Columns in "Transaction" table: assumed to be: Account, TransactionNo, Desc, Date, Month, Quarter, HalfYear, Year, Amount
Assuming SQL 2000, you can write an UPDATE and INSERT "AFTER" trigger attached to the Date field.
You INSERT/UPDATE 25/12/2004 in Date, it automatically updates the Month with 12, Quarter with 4, HalfYear with 2 and Year with 2004.
Within the same TRIGGER you could, also UPDATE or INSERT a record in an AccountBalance Table. Perhaps, AccountNo, CurrBalance.
It depends on the reporting requirements and how responsive the system has to be for online and reporting, but you could have, say, the "pretransaction balance" and "posttransaction" on the Transaction table or "ThisYearsBalanceYTD" in the AccountBalances.
T
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply