Table split ideas

[mcalex]

Hi all

I have a table where the number of fields is causing problems. I want to split the table into 3 or 4 tables with related sections, but am not sure how to maintain the connection to the logical row of data, and how best to do this in Access.

The data relates to council budgets, and all the data is currently in a 'Budget' table. My thoughts are to change this to BudgetIncomeStatement, BudgetRatingStatement, BudgetCashFlowStatement and BudgetNotes as these are the different sections in a budget document.

1: Firstly, should the new tables share a primary key (ie have a BudgetID field in each table that is the same across the 'row') or should each table have its own primary key, with a foreign key relation?

2: If the foreign key relation method, should I keep the 'Budget' table as a main table and have each of the other tables reference it, or should I have BudgetNotes referencing BudgetCashFlow, which references BudgetRating (which references BudgetIncome) as a sort of 'linked list' of tables?

Once I get this sorted, I'll probably be back with 'now how do i do this?' question.

Many thanks in advance
mcalex

 

[PaulO]

My quick thought is that the common link field, bearing in mind it's a Financial Budget, could be the accounting PERIOD be this a Year/Month/Week/Day or any combination thereof. No need to create meaningless additional codes.

 

[plog]

Could you list the fields in your current table? Along with any notes about how they relate to each other.

 

[mcalex]

@plog
ummm, not really. As suggested by the need to split the table, there are quite a few fields (about 120).

The information relates to a council's budget, so the bulk of the data are financial (currency type). There are also a number of boolean, date/time and integer fields related to compliance, due dates, benchmarks, etc. Most of the data is entered directly from the source document, however some fields are calculated by the system - eg checks to see if the sums are correct, and some are determined by the operator - eg whether they've met certain requirements.

As I mentioned in the OP there are a number of statements provided in the documents: cash flow - revenue and expenditure over the period, rating info - the basis for the annual rates charged, income - what used to be called profit & loss, and associated notes. I'm thinking of using these statements as the basis for the table split.

Hope this clarifies things.

@PaulO
I see what you're saying, and might have overlooked this idea as i don't normally use natural keys. I think your post is helping me lean towards the master budget with - as you say - council and reporting period information, and the four statement tables all referencing the master

cheers and thanks
mcalex