Multiple tables or one single table

[darkknight]

Hi all,

I was asked to log each transactions that are happening in our application into DB. Then an administrator can login to the application anytime and export those transactions into a report(.csv). He can select the type of transaction, select the period for those transactions and put it into a report.

Say, I have eight transactions to be logged into the DB. What would be best approach on the number of tables?
Should I be creating eight different tables and use it for individual transactions or create a single table and index for the type and date fields? Which would result in better performance?

Also, If I'm creating one single table for all transactions, there are certain fields that are unique to each transactions and the values for those columns would be blank for the unaffected transactions. Does that occupy unnecessary space as the no.of records are getting increased.

Please advise!


Thanks

[gandolf989]

Start by making a list of all of the attributes that you want to store, think about the cardinality between the various attributes and whether any of them need to be look up values. For example you have a log type column that explains what type of thing is being logged, that would go in a look up table with a description and anything else that you want to know about that log type. Then create table or tables based on what you are storing. But without knowing exactly what data you are logging, no one can tell you how to store it.