Access Table Structure/Relationship design Question!

[starrman]

Hi,

I have a single project that has the following restrictions:
100+ locations
4 major configurations
Task lists will differ between the 4 major configurations.
Many task steps will be unique for many of the sites.

I want to create an app that allows the folowing flexibility for the user.

1. Assign site to Task List (4 mjr configs)(or 100 major cfgs)
2. From a "general pool" of task steps (created for all tasks) have the ability to assign a task step to the custom "site" configuration being built. I need the ability to assign ANY task step to ANY TASK. I also need to be able to use the same pool of task steps to assign the same task step to multiple tasks.
3. I would also like to assign some default task lists with task step assignments completed.

Anyone sharing any idea of the table structure to use would be greatly appreciated.

Dick Starr

 

[DougStevens]

WOW, all I can say is that this is a very in-depth question. Sounds like you're asking for a design solution. Wish I could help, but unfortunately I can't go into any great detail, otherwise I'd be here for the rest of the day.

However, here are a few ideas off the top of the old noggin.

IN BUSINESS TABLES
Locations Table - All the characteristics of a location.

Task Step Table - Individual Task Steps.

Task Configuration - Individual Configuration Records. e.g. Name, Uses, etc.

Task Step Configuration Link - A linking table between [Task Step] table and [Task Configuration] table. This allows the many-to-many relationship between [Task Step] and [Task Configuration]. (Explanation:


DOING BUSINESS TABLES
Location Task - Table (one-to-many from [Location] to [Location Task] using Locations as a Foriegn Key (FK). This will establish the overall "job."

Location Task Steps - Table (one-to-many from [Location Task] to establish the steps involved for the "job." You can append steps from your [Task Steps] table individually. OR create a process that will take a group of Task Steps, defined by a Task Configuration, to be appeneded to this table.



If sounds confusing, I apologize. But, I hope this gives you some insight.

-Doug