Circular relationship nightmare

[Rararachel]

Hi All,

(Have posted this elsewhere, so apologies to anyone who's seen this
behemoth before!)

I have a simple, but surprisingly complicated database requirement in
that the database requires only 5 tables, but includes a circular
reference by necessity. The problem I'm having is that I am unsure how
to create the queries on which to base my input forms so that the
correct data is displayed.

Essentially I have a workflow process that is described in the
following way:
A dataset is released which is deployed to several regions, and each
deployment involves several tasks. Not every region receives every
dataset, but every dataset is built and deployed to at least one
region.
Several physical regions represented in the Region table
fldRegionID - pk
fldRegionName

Several sets of data represented in the Dataset table
fldDataID - pk
fldDataName
fldCycle (this represents the cycle with which the dataset is updated)

Each dataset has several tasks that need to be completed before they
are deployed, some of which are also dependant on the region to which
they are deployed. This is represented by two tables, one (build)
which contains those tasks that are completed for all updates to the
dataset, and the second (deployment) which contains those tasks that
are required for each region's deployment. These are represented as
follows:

- Build table
fldVersionID - pk
fldDatasetID - pk + fk
fldReleaseDate
fldBuildTask1
fldBuildTask2

[J P]

"Rararachel" <gi*******@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Hi All,

(Have posted this elsewhere, so apologies to anyone who's seen this
behemoth before!)

I have a simple, but surprisingly complicated database requirement in
that the database requires only 5 tables, but includes a circular
reference by necessity. The problem I'm having is that I am unsure how
to create the queries on which to base my input forms so that the
correct data is displayed.

Essentially I have a workflow process that is described in the
following way:
A dataset is released which is deployed to several regions, and each
deployment involves several tasks. Not every region receives every
dataset, but every dataset is built and deployed to at least one
region.
Several physical regions represented in the Region table
fldRegionID - pk
fldRegionName

Several sets of data represented in the Dataset table
fldDataID - pk
fldDataName
fldCycle (this represents the cycle with which the dataset is updated)

Each dataset has several tasks that need to be completed before they
are deployed, some of which are also dependant on the region to which
they are deployed. This is represented by two tables, one (build)
which contains those tasks that are completed for all updates to the
dataset, and the second (deployment) which contains those tasks that
are required for each region's deployment. These are represented as
follows:

- Build table
fldVersionID - pk
fldDatasetID - pk + fk
fldReleaseDate
fldBuildTask1
fldBuildTask2
.
.
.
fldBuildTaskn

- Deployment table
fldVersionID - pk + fk
fldDatasetID - pk + fk
fldRegionID - pk + fk
fldDeployTask1
fldDeployTask2
.
.
.
fldDeployTaskn

Because each region may or may not receive an update, and the
relationship between datasets and regions is m-m, a fifth,
RegionDataset table is required:
fldDatasetID - pk + fk
fldRegionID - pk + fk

This table's values are essentially static.

PHEW!!

I have been through this structure several times and tried to re-jig
things to avoid the circular relationships created, but none of the
options that I have tried suits the requirements.

Where I have a problem is when I try and pre-populate items in the
deployment table. I don't know how to set up my queries so that only
valid deployment records may be added, given the values in both the
Build table and the RegionDataset table.

EG:

RegionA receives dataset updates for Dataset1 and Dataset4
RegionB receives dataset updates for Dataset1, Dataset2 and Dataset3

This is represented in the RegionDataset table by the following:
RegionA-Dataset1
RegionA-Dataset4
RegionB-Dataset1
RegionB-Dataset2
RegionB-Dataset3
Dataset1 has a version update v111, so a new build record is created
with a compound primary key:

Dataset1-v111

Once this entry is made, I'd like my database to be able to
automatically add the only two valid entries to the Deployment table:
RegionA-Dataset1-v111
RegionB-Dataset1-v111

But my queries must be incorrect, because this does not work.

Thanks to anyone who has read this far!

If anyone has done something similar before or can point me in the
right direction it would be greatly appreciated. I have searched
I-don't-know-how-many helpfiles, newsgroups and websites, but without
any luck.

Sounds like one of those 'IQ' questions you get from your college
professor - if A leaves the station at 5:00 traveling 30 mph and B leaves
from the opposite end at 5:30 traveling 45 mph, how long does it take before
they meet head on in a fiery collision. You seem to be making this into
some kind of 'only a genius can do it' problem. Don't over think the
process or the obvious becomes vague and indistinquishable.
RegionA-Dataset1
RegionA-Dataset4
RegionB-Dataset1
RegionB-Dataset2
RegionB-Dataset3

Everything you need to accomplish the task is summed up in that simple
table. Apply some logic to what your doing and you will solve the problem.

JP

[Chris2]

"Rararachel" <gi*******@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

Hi All,

<snip>
Several physical regions represented in the Region table
fldRegionID - pk
fldRegionName

Several sets of data represented in the Dataset table
fldDataID - pk
fldDataName
fldCycle (this represents the cycle with which the dataset is updated)

<snip>
- Build table
fldVersionID - pk
fldDatasetID - pk + fk
The Dataset table doesn't have a column named fldDatasetID. Is this
column fk'd to Dataset.fldDataID, or somewhere else?

fldReleaseDate
fldBuildTask1
fldBuildTask2
.
.
.
fldBuildTaskn
These columns are repeating. This goes against first normal form
(1NF), and can cause many problems.

The table should be:

Build:
fldVersionID - pk
fldDatatsetID - pk & fk (to Dataset?)
fldReleaseDate

BuildTasks:
fldBuildTaskID - pk
fldVersionID - \ fk (to Build)
fldDatasetID - / fk (to Build)
fldBuildTask

With no number after the "BuildTask". One row equals one task.


- Deployment table
fldVersionID - pk + fk
fldDatasetID - pk + fk
fldRegionID - pk + fk
fldDeployTask1
fldDeployTask2
.
.
.
fldDeployTaskn
Ditto with Build, in that there are repeating columns. They should
be removed to another table with a single column for fldDeployTask.
Also, I am not sure I would have separate tables for Build and
Deployment. It just seems like a massive case of duplication.

Although, with my limited knowledge based on the narrative, it is
quite difficult to say: In order to have the general "template", I
would look at RegionID and see if I couldn't use "template" as the
fldRegionName for the "general" template (or some other similar
value), essentially making it my origin list with all tasks, with
the actual regions having different names and limited sets of task
lists. This keeps it all in the same table (my preference). (You
might even have multiple templates by creating multiple regions to
serve this pupose: test template region, development template
region, production template region, etc.; although, again, the
advisability of that is difficult to know in this case.)

BuildTasks:
fldBuildTaskID - pk
fldVersionID - \ fk (to Build)
fldDatasetID - / fk (to Build)
fldRegionID - fk (to Region)
fldBuildTask

In fact, I'd rename it:

Tasks:
fldTaskID - pk
fldVersionID - \ fk (to Build)
fldDatasetID - / fk (to Build)
fldRegionID - fk (to Region)
fldTask

Tasks replaces Deployment entirely. Build would contain the release
date and other information related to the particular release, and
not the individual tasks.

<snip>
Where I have a problem is when I try and pre-populate items in the
deployment table. I don't know how to set up my queries so that only valid deployment records may be added, given the values in both the Build table and the RegionDataset table.

EG:

RegionA receives dataset updates for Dataset1 and Dataset4
RegionB receives dataset updates for Dataset1, Dataset2 and Dataset3
This is represented in the RegionDataset table by the following:
RegionA-Dataset1
RegionA-Dataset4
RegionB-Dataset1
RegionB-Dataset2
RegionB-Dataset3
Dataset1 has a version update v111, so a new build record is created with a compound primary key:

Dataset1-v111

Once this entry is made, I'd like my database to be able to
automatically add the only two valid entries to the Deployment table: RegionA-Dataset1-v111
RegionB-Dataset1-v111

But my queries must be incorrect, because this does not work.

Although meant for an SQL Server newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

We've already got the main idea for your tables (although DDL would,
of course, be fantastic).

Time now for your sample data, your desired results, and SQL code of
your queries created to date, and the examples of how things went
wrong.
Sincerely,

Chris O.