Database design and adding new records

[Guest]

Hi

Within my database, I have the following tables:

Division Table
*DivisionID
DivisionName

Branches Table
*BranchID
BranchName
DivisionID

Employees Table
*EmployeeID
EmployeeName
BranchID

Each Division may have several branches; each Branch may have several
employees.

However, there are some Branches that every Division must have. So, I
created another table:

DefaultBranches Table
*DefaultBranchID
DefaultBranchName

And, I added a new field to the Branches table
DefaultBranchID

In my code, when I create a new Division, I create several new Branch
records to correspond to the records in the DefaultBranches table.

So far, so good.

Except that, Divisions may have non-standard Branches, unique to a
particular division.

When my code tries to create a new record in the Branches table, I get an
error message
"You cannot add or change a record because a related record is required in
table 'DefaultBranches'. I'm assuming that my problem is the join between the
Branches and the DefaultBranches table. I've tried all 3 kinds of join, and
get the same error message each time.

So my questions are:

1. Is there a better way to create these 'default' branch records?

2. If this way is OK, how do I create a record for a non-default branch?

Dave

 

[Allen Browne]

Dave, the concept of a table of default branches that is automatically added
to the Branches table whenever you add a new Division makes perfect sense.

There will be no relation between Branches and DefaultBranches.
DefaultBranches exists only to teach the database what the most commonly
expected branch names are. Hence, you should be able to add the branches
*after* a new division is added.

You probably want to use the AfterInsert event of the form where branches
are added. In that event, execute an Append query, using the new division
number (in the form), and SELECT the names in DefaultBranches.