typical business relationship question

[Eric]

I'm sure this is an easy one for lots of you out there, but...

1) Department --> Division
A Department can have 0 to * Divisions

That's the reality of it, anyway. Now the question - Employee:

If a department has divisions, then the employee works for one of them.
Obviously, if the Department does not have divisions, the Employee belongs
to the Department.

I don't want to create a convoluted relationship that needs to be reinforced
with code. My gut instinct then is to have the Division default to something
like "N/A" and do:
- A Department has 1 to * Division
- A Division has 0 to * Employee

Any comments and tips would be much appreciated.

BTW, the application is for a help ticketing system, specialized for a
hotel. Anyone know of any good templates out there I could use as a starting
point?

Much TIA,

Eric

 

[Allen Browne]

So the entity that an employee works for could be a department, or it could
be a division? That suggests that departments and divisions are instances of
the same kind of thing in your scenario. Would it be possible to create a
table that handles them both as the same kind of thing?

tblEntity
EntityID AutoNumber
EntityName name of the department or division
ParentEntityID Number

Using this table, you can create a record for a Department. It will have
nothing in its ParentEntityID field. Then create another record for a
Division, and use the ParentEntityID field to record the EntityID of the
department this division belongs to.

You can now set up a relation between this table and the employee table,
such that an employee can work for either one.

If necessary, you can also put companies in as entities, so a department
belongs to a company. In the end, the employee can work for a company, a
department, or a division.

Hopefully that's useful for what you need to do.

 

[Eric]

Yes, that is the ticket I think. That's also a variation on the
Employee/Manager type of relationship, yes?

Wouldn't that typically done by having a table with a relationship to itself
though?? Can access do that? Or did you just in fact tell me how, and leave
out the relationship to itself?

Does that complicate forms and reporting?

Sorry to be such a pain, but I haven't done the db thing or played with
access in a million years, and I've gotten suckered into building an
application that needs to done yesterday.

Thanks for getting me that far though!

Eric

 

[Allen Browne]

Yes, you are right, Eric.

You can create a self-join (relation between the table and itself), by
dragging a 2nd copy of the table into the Relationships window (Tools menu).
Access will alias the 2nd copy as "Entity_1". Then drag the primary key from
one onto the foreign key on the other, and Access opens the dialog where you
check the Referential Integrity box and create the relation.

 

[Eric]

Thanks again, Allen. I also found your tip on using self joins on your
website. (It looks a little intimidating right now because I see all of
these duplicated tables, but then again, I haven't read it yet).

Jamie's nested sets idea does sound more elegant, but I didn't see anything
about how to implement this in Access. Have you any experience with doing
nested sets in Access?? Tips?

Thanks!

Eric

 

[Allen Browne]

Eric there probably is not anything I can say in response to your question
without provoking Jamie, so I won't bite on that one.

The complex example you refer to involves many generations of self-join. You
don't need anything that involved unless you are trying to track a
multi-national that has a national office, state offices, local offices,
departments, divisions, etc.

 

[Eric]

Thank you sir

Eric

Eric there probably is not anything I can say in response to your question
without provoking Jamie, so I won't bite on that one.

The complex example you refer to involves many generations of self-join.
You don't need anything that involved unless you are trying to track a
multi-national that has a national office, state offices, local offices,
departments, divisions, etc.