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:
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.