Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm creating a database to keep track financial information for about 30 different accounts. So far this has all been handled with each account as its own Excel table with the different years across the top, and the and ledger lines along the side. All of the tables are identical.

I want to put this into a database to make it more manageable, but I'm not sure if I have the best structure. Here are the basic table structures:

ledger_lines(ledgerCode, ledgerName)

account_X(accountCode, ledgerCode, year1, year2...)

With the bold bold as the primary key and ledgerCode referencing the ledger_lines table. Also, there would be 30 of the account_X tables eg. account_1, account_2... for all of the different accounts, all referencing the ledger_lines table.

Is this the best structure for managing this database? For example, and new field will have to be added to every table at the beginning of every year and to compare ledger lines across the accounts I will have to query all of the tables.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

A separate table for each account will be difficult to work with. For example, if you need aggregate values spanning all 30 accounts, you would need to look in all 30 tables. And if you need aggregate values for only a subset, say 5, of those accounts you would need to look in just 5 tables but then you must specify the names of those 5. It gets too darn fiddly fast.

You should consolidate those account tables into a single Accounts table which includes a field to identify which account each row is associated with. And when you want to see all the information for any one account, instead of an actual table, you can dynamically create a virtual table with an appropriate SELECT query.

SELECT [field expression list]
FROM Accounts
WHERE acct_num = 32;

Also think about happens when/if you need to modify the table structures. You really don't want to have to make the same change to 30 tables. BTW, I'm also uneasy about needing to add a new column each year. Aim for a design which doesn't require that type of change. Store information for different years by row rather than by column. A rule of thumb is "columns are expensive; rows are cheap". Try to work cheap! :-)

share|improve this answer
    
I started with having everything in one table, but moved away when when I became unsure of how to create a primary key. With everything in one table and using only the fields I have, the only unique identifier would be to make a composite key from the the ledger line, the account and which year the value belonged to. That seemed cumbersome and maybe bad practice (I'm not sure). Or I could add an auto number to each line, but that would be unnecessary data. However, I do see now its much easier to use with everything together. –  user1290426 Jul 12 '12 at 2:10
1  
I would have a [FiscalYear] table as well. With 30 accounts, 30 years and 1000 LedgerCodes, your bigger table would be less than 1M rows. Not a problem, with whatever the clustered key you choose, either the compound one or the (surrogate) auto-generated. The 3-column compound would have to be either UNIQUE or PRIMARY key. –  ypercube Jul 12 '12 at 9:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.