Table structure for keeping track of multiple similar accounts

Question

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.

Answer 1

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! :-)