Table design advice - one-to-one or not?

[Jack MacDonald]

Hi

A colleague is working on a database for storing and querying results
of various productivity studies. He is an Access novice, but wants to
(mainly) build this database for himself. My initial recommendation
was to set up three major tables for him:

tblCommon
- PK StudyID - autonumber
- about 50 fields that are common to two types of studies

tblDetailedStudy
- PK StudyIDfk - longInt
- about 50 fields that are specific to "detailed" studies

tblShiftStudy
- PK StudyIDfk - longInt
- about 50 fields that are specific to "shift-level" studies

A particular study site (ie, a record in Common) can have either a
Detailed study or a Shift-Level study, or both. The database will grow
with time, but will probably top out at about 1500 records. This will
be a personal database -- no end-users except for my colleague.

He ran into problems with this three-table approach. I had showed him
how to make a single form for tblCommon, with two subforms for the two
subsidiary tables. It worked well until he wanted to apply filtering
and sorting to some of the fields in the subforms. Of course, that did
not work because each subform contained only one record - the one
related to the current record in the main form.

Then I tried to create a single query using outer joins that displayed
all the fields and records from all three tables. Worked well to
display the data, but was non-updateable because of its two outer
joins, and therefore useless for data entry.

My next thought (not implemented yet) was to create two queries and
two forms that dealt with (Common + Detailed) and (Common + Shift)
data. These two forms would be accessed completely independently from
one another, and the fact that two studies shared the same Common data
would appear to be coincidental. Under this scenario, if my colleague
was viewing a Detailed study and wanted to check whether there was a
corresponding Shift-Level study, using two separate forms would make
that process somewhat awkward.

I guess the ultimate "inelegant" approach would be to dump everything
into a single table, and treat it like a spreadsheet, but I have a
hard time recommending that...

I am leaning towards recommending the two-query, two-form approach for
its simplicity, but it bothers me for some reason. Perhaps it seems
clunky...??

Can anybody offer any insight or advice?

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Anand.V.V.N]

Hi Jack,

I think the 3 table approach would be better. If you wite queries to update
the fields and adding rows, of course the code would be more I geuss
depending on the number of fields you have to update or add into the
database, but the advatbge would be that you can update or add to database no
matter what kind of join you use.

Hope you found this helpful.

Anand

 

[John Vinson]

Hi

A colleague is working on a database for storing and querying results
of various productivity studies. He is an Access novice, but wants to
(mainly) build this database for himself. My initial recommendation
was to set up three major tables for him:

tblCommon
- PK StudyID - autonumber
- about 50 fields that are common to two types of studies

tblDetailedStudy
- PK StudyIDfk - longInt
- about 50 fields that are specific to "detailed" studies

tblShiftStudy
- PK StudyIDfk - longInt
- about 50 fields that are specific to "shift-level" studies

I'm VERY uncomfortable with this design: not because of the one-to-one
relationship for subclassing, but because of these fifty fields. I
very strongly suspect that you're storing data (questions, perhaps?
are these studies questionnaires?) in fieldnames. 30 fields is a VERY
wide table, and 150 fields - which you have in the aggregate here - is
way beyond the bounds of normal design.

Might it not be better to split this out? What *are* some of the
typical fields in these three tables? Might they be better as *rows*
in a one-to-many rather than as *fields*?

A particular study site (ie, a record in Common) can have either a
Detailed study or a Shift-Level study, or both. The database will grow
with time, but will probably top out at about 1500 records. This will
be a personal database -- no end-users except for my colleague.

1500 records is tiny. If you make this "tall-thin" and end up with
1500 studies * 150 records for each study = 225000 records, it's
getting respectablebut is still of a size that Access can handle with
aplomb.

He ran into problems with this three-table approach. I had showed him
how to make a single form for tblCommon, with two subforms for the two
subsidiary tables. It worked well until he wanted to apply filtering
and sorting to some of the fields in the subforms. Of course, that did
not work because each subform contained only one record - the one
related to the current record in the main form.

Then I tried to create a single query using outer joins that displayed
all the fields and records from all three tables. Worked well to
display the data, but was non-updateable because of its two outer
joins, and therefore useless for data entry.

My next thought (not implemented yet) was to create two queries and
two forms that dealt with (Common + Detailed) and (Common + Shift)
data. These two forms would be accessed completely independently from
one another, and the fact that two studies shared the same Common data
would appear to be coincidental. Under this scenario, if my colleague
was viewing a Detailed study and wanted to check whether there was a
corresponding Shift-Level study, using two separate forms would make
that process somewhat awkward.

A Form with two Subforms would be better if you are determined to go
with this table structure. You can put the two subforms on separate
Tab pages if you need the screen space (and, with 50 fields, you
surely will!)

John W. Vinson[MVP]

 

[Anand.V.V.N]

Hi John,
After reading what you posted I too kind of think the same thing you suspect.

Could it be that all the fields can be stored in one table as in and have
forms designed in a categorical way i.e. group similar questions together
into a frame, and haev a wizard kind of interafce with next, previous
buttons, and store the final result into a table with the field ids and the
corresponding values, not in one table but accross tables again grouping
similar type of information?

Anand

 

[Jack MacDonald]

I'm VERY uncomfortable with this design: not because of the one-to-one
relationship for subclassing, but because of these fifty fields. I
very strongly suspect that you're storing data (questions, perhaps?
are these studies questionnaires?) in fieldnames. 30 fields is a VERY
wide table, and 150 fields - which you have in the aggregate here - is
way beyond the bounds of normal design.

Might it not be better to split this out? What *are* some of the
typical fields in these three tables? Might they be better as *rows*
in a one-to-many rather than as *fields*?

In general, I tend to agree with you. However, I am trying to walk a
fine line here between teaching him about normalization and just
getting the job done with the easiest structure for him to understand
and manage. I likewise suspect that there *could* be fields to split
out, but then he has one more table to manage times the number of
different items to split out.

Maybe it comes down to the old proverb about "give a man a fish and he
can eat for the day, teach him how to fish and he can eat forever".
Question is -- does he want to learn to fish and who's going to teach
him?

As for your question about the table contents -- no, they are not
questionaires. They are the results of certain productivity studies
about timber-harvesting equipment. The fields are about certain
measurements that are taken for each of the studies. I haven't
examined them to see where they might benefit from normalization.

1500 records is tiny. If you make this "tall-thin" and end up with
1500 studies * 150 records for each study = 225000 records, it's
getting respectablebut is still of a size that Access can handle with
aplomb.

Yes - the database is tiny which also goes toward just how much effort
should be made in getting the structure "right"? For a big database,
normalization is clearly advantageours for storage efficiency. For a
tiny database, a flat structure just might be easier for a novice to
implement and understand.

A Form with two Subforms would be better if you are determined to go
with this table structure. You can put the two subforms on separate
Tab pages if you need the screen space (and, with 50 fields, you
surely will!)

Are you suggesting the subforms use Child/Master links to the main
form, or they simply exist on their own tabs? If it's the former, then
I don't see how to solve the original problem of not being able to
filter on the subform without resorting to a lot of VBA code (which is
beyond his capability).

If it's the latter, then that's not so much different than separate
forms, although it *does* open the possibility of synchronizing the
record navigiation of the two subforms.

Thanks for your input.

John W. Vinson[MVP]

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Pat Hartman]

"Yes - the database is tiny which also goes toward just how much effort
should be made in getting the structure "right"? For a big database,
normalization is clearly advantageours for storage efficiency. For a
tiny database, a flat structure just might be easier for a novice to
implement and understand."
The number of rows a table will ultimately contain has nothing to do with
whether or not you should expend the effort to properly normalize the
structure. A poorly normalized small table will be just as difficult to use
as a poorly normalized large table.

It is important that you understand the ultimate usefulness of a normalized
structure. The whole point is that when your data is properly structuctured,
you will be able to create meaningful reports with simple queries. When your
data is not normalized, you will need many extra queries and probably VBA
also.

Take a look at what your friend wants out of the database before you elect
to just resign him to a flat structure and determine the SQL/VBA neccessary
to obtain the results with both flat and normalized tables.

 

[Tim Ferguson]

"=?Utf-8?B?UGF0IEhhcnRtYW4=?=" <[email protected]>
wrote in
PMFJI but ...

"Yes - the database is tiny which also goes toward just how much effort
should be made in getting the structure "right"? For a big database,
normalization is clearly advantageours for storage efficiency.

I don't think I buy into this. The advantage of relational design, and
normalisation in particular, is not storage size. Firstly, storage is
cheap as dirt nowadays; secondly, a fully-normalised design is often
bigger than some other supposedly-equivalent solution.

The reason for the success of R theory is that it is maths-based;
provable; and objective. This adds up to integrity -- the fact that a
datum is stored exactly once, in one place means that it cannot be
contradicted by other data somewhere else. The problem with flat files is
that in one record, "ACME Records" has an address on Sunset Avenue, and
in another record it is in Brideswell Drive. Which do you believe? Are
they the same ACME Records? Et cetera. True R design solves a number of
insert, update, and delete problems that are characteristic of non-R
designs.

For me, the accuracy of a database is paramount. I can fix user interface
problems; performance issues can be addressed by faster hardware; but a
design that returns wrong information is plain useless, if not worse.

Just a thought...


Tim F