Creating Database Representing Two Chart of Accounts

[Chart Builder]

I am creating an Access 2007 Database that will need to model a global chart
of accounts for a multinational firm I am currently contracting for. There
are two types of charts... one is a group chart that has accounts that are
only 6 digits long (e.g. 600000) and the second is a more detailed operating
chart with 10 digit accounts (e.g. 6000001111). All accounts in the operating
chart are required to contain a field representing the 6 digit group account
that it rolls up to (e.g. 6000001111 would contain a group account field with
the value 600000). Other than the requirement of a Group account mapping to
Operating account both tables would be exactly the same. Is this something
that should be modeled with 2 separate tables or should all the account data
be within one table with special validation rules in place to distinguish
between Operating and Group accounts?

 

[Allen Browne]

So, for any account, you need to handle 2 things:
- the group it belongs to (required)
- the operation it belongs to (may not apply.)

This would suggest 2 fields in your table:
- AccountGroup is required, and would be a foreign key to a table of account
groups.

- AccountOperation is nullable. (You can still make it a foreign key with
referential integrity.)