Advice Needed

[Martyh]

Hi all,

I'm looking for some advice: the problem is that I've got 13 sections in which people are working, and administrative assistants that are taking care of 1 or more of these same sections.

In addition to the usual security, there is also a requirement to make the AA's only output their own sections reports.

The way I see it there are two ways to ensure this:

1) put in a field in the users table that would indicate which sections the AA was responsible for ie

userID 1
respFor 56,34,45

userID 2
respFor 41,3

userID 3
respFor 42

... etc respFor would have to be a text field with a delimiter between each sectionID

sectID 41
sectName AAA

sectID 42
sectName BBB

2) have a separate table that will form a "union" between user and section tables ie User&Section

userID 1
sectID 56

userID 1
sectID 34

userID 1
sectID 45

userID 2
sectID 41

userID 2
sectID 3

userID 3
sectID 42

Now for my question: Can you advise me on which way is best for this circumstance? or perhaps you've got another way to go? Have you got an example ?

Your advice please...

TIA

 

[boblarson]

Actually, if you want a normalized database (and I'm hoping you do), then you should have a table to hold the assistant details, since it is really a one-to-many situation. Each assistant has responsibility over multiple sections. So, you would want to have tables like this:

tblSections
SectionID - Autonumber (PK)
SectionNumber - Integer (this could become the PK and replace the SectionID if you wished)
SectionDescription

tblSectionAssignments
SectionAssignID - Autonumber
userID - FK
SectionAssigned - FK - Long Integer (if using the autonumber from above, or Integer if using the other)