One to One relationships

  • Thread starter Thread starter CDK
  • Start date Start date
C

CDK

I have created a database which is basically a direct
copy of one written in Lotus Approach format.

I have been told that one to one relationships are rare,
but I think I need them to suit my situation.

Basically The database stores records of Runs carried out
on a small chemical plant unit, each Run is unique and so
the Primary Key in the main table is set as the Run
Number.

A recipe(feed) sheet is required to set parameters for
the run, these parameters are mainly included in the feed
sheet table.

The recipe sheet also includes calculated fields shuch as
flow rates, weights etc. These are held in the Recipe
query which is based on the recipe table.

Up to this point I do not have any problems but now I
need to introduce additions which are confusing me.

The database also contains a results page where
information from the run is stored which is relative to
the feed sheet, this again proves not to be a problem.

I now need to provide extra feed sheets that are
basically copies of the original feed sheet but can be
changed so that during one run we can have two sets of
conditions.

I have three tables labelled feed table 1-3, three
queries labelled 1-3 and three forms labelled 1-3. The
only difference in their contents is the number (1-3)
which the conditions relate to.

Do I need to have feed 1 on a main form and feeds 2&3 on
Subforms, if so how do I relate them,what relationships
do the queries need?

The three feed periods will be reported as one run in the
results form so I don't need three results forms.

Each recipe sheet contains about 70-90 fields showing run
temperatures so the forms are quite full.

At the moment it seems to be the queries where I am
getting stuck

Any help is appreciated.

Thanks

Craig.
..
 
I have created a database which is basically a direct
copy of one written in Lotus Approach format.

I have been told that one to one relationships are rare,
but I think I need them to suit my situation.
Click to expand...

Typically one can simply include all the fields from both tables in a
single table, unless there is some compelling reason to go to the
added complexity of a relationship.
Basically The database stores records of Runs carried out
on a small chemical plant unit, each Run is unique and so
the Primary Key in the main table is set as the Run
Number.
Click to expand...

Ok... that's cool...
A recipe(feed) sheet is required to set parameters for
the run, these parameters are mainly included in the feed
sheet table.
Click to expand...

Now here it SOUNDS like you may in fact have a one (recipe) to many
(runs) relationship. Or is there a new, distinct, unique Recipe
unrelated to any other recipe for each run?
The recipe sheet also includes calculated fields shuch as
flow rates, weights etc. These are held in the Recipe
query which is based on the recipe table.
Click to expand...

These of course should be just calculated, not stored - sounds like
you're doing that.
Up to this point I do not have any problems but now I
need to introduce additions which are confusing me.

The database also contains a results page where
information from the run is stored which is relative to
the feed sheet, this again proves not to be a problem.
Click to expand...

Again... is there ONE AND ONLY ONE result from a Run? No statistics,
no time points, no replication?
I now need to provide extra feed sheets that are
basically copies of the original feed sheet but can be
changed so that during one run we can have two sets of
conditions.
Click to expand...

A one (run) to many (feed sheet) relationship. NOT a one to one
relationship.
I have three tables labelled feed table 1-3, three
queries labelled 1-3 and three forms labelled 1-3. The
only difference in their contents is the number (1-3)
which the conditions relate to.
Click to expand...

This is A VERY BAD IDEA. You're embedding data - a feed condition - in
a table name. What if someday you need FIVE feed sheets? or seven, or
eight?

It sounds to me like you need a One to Many relationship, with a
ConditionNumber field. Rather than three *tables* you would have three
*records* in this table, with ConditionNumber 1, 2, and 3 - or 4, or
7.
Do I need to have feed 1 on a main form and feeds 2&3 on
Subforms, if so how do I relate them,what relationships
do the queries need?
Click to expand...

I'd say one form and one subform, showing multiple records.
The three feed periods will be reported as one run in the
results form so I don't need three results forms.

Each recipe sheet contains about 70-90 fields showing run
temperatures so the forms are quite full.
Click to expand...

If these are temperatures at different time points THEN AGAIN, you
have a one (run) to many (temperatures) relationship. Storing data
(time points?) in fieldnames is just as bad as storing data in
tablenames!
At the moment it seems to be the queries where I am
getting stuck
Click to expand...

I think you're having difficulty because you are using non-normalized
tables; this is very easy to do if your table design is driven (as it
often is) by existing paper forms (with a column for each time point)
or by spreadsheets. Step back, and identify the Entities (real-life
things or events, such as a temperature measurement) important to your
system; each such entity should have its own table.
 
Back
Top