Table design

[cocalota]

Hi,

I am new in Access 2010 and need help in table design schema.

Here is the short version. I am creating a database to track budget changes within our organizations.

1. We have 3 division (West, East and Central).
2. Each division will have 5 Organizations (Office, Campus, Operations, IT and HR)
3. Each Organizations will have multiple sub-organizations
4. The budget start with the sub-organizations - which is break down by cost center and sub cost center.

This is the design I came up and hope someone can give me a feedback or suggestions.

tblFiscalYear
tblDivision
tblOrganizations
tblSubOrganizations
tblCostCenter
tblsubcostcenter (budget table)

tblDivision --1 to Many --> tblOrganizations

tblOrganizations --1 to Many --> tblsuborganizations

tblsuborganizations --1 to many --> tblCostCenter

tblCostCenter --1 to many --> tblsubcostcenter

Is there a better way to design this?

 

[MarkK]

You can make a self-referencing table, that is, the foreign key points to a primary key in the same table, so . .

tblOrgLevel
OrgLevelID (PK)
ParentID (FK to parent OrgLevelID)
LevelName
LevelType

. . . and data might be . . .

1/0/East/Division
2/1/Office/Organization

. . . since all you seem to need to do with the organizational layers is store them in what is essentially a tree structure. Fiscal year, however, doesn't belong in this table. Fiscal year should be in a separate table that links to the Organization and contains the data for that year.

 

[plog]

First, I don't think tblFiscalYear is necessary. What will be the fields of this table?

Next, I don't think its a straight drill down (Division -> subCostCenter). I think its a 2 sided drill down that meets in a junction table using the bottom of each side. One side would be divisional:

Divisons -> Organizations -> SubOrganizations

The other side would be accounting:

CostCenter -> SubCostCenter

Then you have a junction table that combines the 2 sides at their lowest levels (SubOrganizations, SubCostCenters). For examples sake this is what that junction table would look like:

tblBudget
SubCostCenterID, SubOrganizationID, BudgetAllowance, FiscalYear
17, 29, $40000, 2014
19, 29, $500, 2014

That structure would allow you to roll data up each side to the level you wanted.

 

[cocalota]

Attached is my current table design and any feedback will be appreciated. I need to know how to streamline the 1:M table design.

 

[plog]

You followed neither of our suggestions. And from what I see, it seems like my initial suggestion was sound advice.

Why do you have cost center table linked to SubOrganizations, but also have costcenter and costcenterdesc fields in the Organization table?

Another big thing are the fields called lastfybudget and nextfybudget. That's not the way to structure your data. I don't know if these fields will contain amounts or the year the data is for, but neither is correct. If you want to reference a difference years data you do so in a query. This though is a bridge to cross when you have your tables properly structured.

Lastly, and probably most importantly--don't be that Access developer--help the next guy down the line. Your field names make sense to you, but they aren't going to initially make sense to whomever works with this after you--use the description area in the table's design view to add notes to what each field holds. That will help the next guy, as well as us who have no idea about your data is for.