Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
perryMason
Starting Member
1 Post |
 Posted - 2004-08-06 : 17:51:31
|
| I am re-designing an existing custom accounting system. Currentlywe have two revenue streams; streamA, and streamB: each being two different products/services. an instance of streamA will generate a line in the Transactions table, as will streamB. The two streams have different information associated with them.Currently if we want to know the information about a streamB Transaction we just use the TransactionID to get all columns from the Transaction row for that ID. Then the application asks for the correct fields and displays them. We do the same for a streamA Transaction.The first problem is this: There are alot of NULL columns in the transaction table because streamA Transactions don't use the streamB info and viceversa. Also I tend to want to design most of the application in the database so that queries return exactly what the application needs and nothing more.The second problem is this: We will be adding new revenue streams soon, and logically the # of columns in the Transaction table will need to grow.My boss suggests that we put a typeID in the Transactions table and create a TransactionTypes table. Then, we make auxillary Info tables that store only the appropriate infor for each Stream... StreamATransaction_Info, StreamBTransaction_Info, etc.This is still not a good idea i feel, because you won't know which table to search for the aux info in; you've passed the query a transactionID. and it does not know (without using conditional statements) which table to search through. anyway with each new added Revenue Stream we will just add aux Info tables.Maybe the solution is to create the info tables, then pass the transactionID to a query. The query gets the typeID and then conditionally builds the query as a giant string and then executes it at the end.Any ideas? Thanks in advance!!! |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-08-06 : 18:08:21
|
| I agree with your boss. In your stored proc, just do a SELECT CASE or big IF block to execute the right code to get the info you need.No need for dynamic SQL server.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 23:20:24
|
| Hey perry, as a matter of fact I have that issue too, and i just finished designing that part of the db last week. As with your problem, I felt that having the many columns in one table, even if some info was to be null, was a btter design for me than to have seperate tables for each. For example, a transaction can be made by credit card, or a check, and some that won't even have either (maybe a cash sale which needs no additional client info), and I need totally different information for each. I also have a transactiontype so i know what info to use (cc#, exp date, or bank routing number, checking account number etc.) I'm not sure if the design was any good performace-wise but I'm happy with it (so far) and don't need to worry about which other tables to access. The thing is for my case, is that I will not need to add other info, only have about 12 columns, as you may need for yours. So maybe Michaels's approach is better. I think I prefer the IFs over the CASEs, but that's a personal pref.Wish you luck.- RoLY roLLs |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-07 : 12:17:55
|
| Genereally, you would go with one transaction table. You then have "detail tables" split off of that which are specific to each type of transaction. All the transactions will have some core data. The various transactions though might have their own data specific to that type. This lets you centrally track transactions, while still normalizing to avoid NULLs, save space, and maximize design.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
|
|
Accounting System Design Issue