Design for Multiple lookup

[Dan Cullin]

Designing a database to manage Project history. I'll
describe a sample.
There are 15 people who might be involved in the project.
Each project will have 3 to 10 of these people working on
it. I would like to design it so that 3 years from now I
can ask the system which project did person X work on.
In addition I want to have a list of keywords assigned to
each project so I can lookup by keyword.

At this point I'd like a suggestion on how to. Where can
I look to learn more. It seems like a perfect problem to
solve in Access.

Dan

 

[Tim Ferguson]

There are 15 people who might be involved in the project.
Each project will have 3 to 10 of these people working on
it. I would like to design it so that 3 years from now I
can ask the system which project did person X work on.
In addition I want to have a list of keywords assigned to
each project so I can lookup by keyword.

I make it five tables:


People --< AllocatedTo >-- Projects

Projects --< BelongTo >-- Keywords


If people move on and off projects, and you want to know "who was allocated
to what on a particular date", then you'd need fields like "AssignedDate"
and "RemovedDate" in the AllocatedTo table.

For the Keyword table, I would be tempted to use just a single shortish
(12?) text field without a separate numeric key field. The reason for this
is that the linking field BelongTo.Keyword will be the actual keyword
itself, and you would not need to actually make the join when you want to
look them up.

If this does not make any sense or you want more details, do post back.

Best wishes


Tim F