May 18, 2011 at 10:32 am
I have been given the task of designing a Transaction database that will contain 37 different types of transactions. I have got as far as identifying the 37 types and grouping them in to subsets for example:
***************************
Invoice (Heading)
***************************
Invoice
Credit Note
Pro Forma
Quotation
Advice Note
Back Order
***************************
Stock Posting (Heading)
***************************
Stock Posting
Order
GIN
Receipt
Return
***************************
Cash Posting (Heading)
***************************
Cash Posting
Petty Cash
Purchase Invoice
Purchase Credit
Essentially I will have the following table structure:
TransactionHeader (every single transaction will be inserted in to this table - contains the common information)
TransactionHeaderID PK
TransactionTypeID FK
Transaction Date...
TransactionLine (every transaction line will be inserted in to this table - contains the common information)
TransactionHeaderID FK
TransactionLineID PK
RecordTypeID FK...
My question is, do I create generic tables, with alot of redundant columns for each type e.g.
InvoiceHeader
InvoiceLine
InvoiceCommentLine
InvoiceFooter
CashPostingHeader
CashPostingLine
StockPostingHeader
StockPostingLine
Or, do I get specific and go a level down and create separate tables for each transaction type?
Does anyone have any advice?
May 18, 2011 at 1:22 pm
craigbroadman (5/18/2011)
...My question is, do I create generic tables, with alot of redundant columns for each type e.g.
...
Or, do I get specific and go a level down and create separate tables for each transaction type?
If you create separate tables, you will also have a lot of redundant columns. In fact, all the common column types will be duplicated 37 times.
Also consider this: how are you going to retrieve the info, by separate transaction type or as a whole? If you separate the tables, then a simple report that needs a transaction total over a time period will need to union the 37 tables.
I maintain a systems that has time logs, consumable logs, and miscellaneous logs broken into their own tables. Every billing procedure, report, view, etc. has to combine the logs before it can do its work. I can't imagine it being 10 times worse.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 19, 2011 at 5:37 am
I would not lay out the database by the transactions defined. Instead I would to set up standard database normalization, taking it out to 3rd normal form. It's a good approach for data storage, especially in a transaction processing system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2011 at 12:04 pm
I'm in agreement with Grant.
You do not model after transactions but after entities and relationships, after your ER model is sound then you move to physical implementation.
I also agree 3NF is the safest bet for a OLTP system.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply