Subclassing; one-to-one relationships

[BecWithFS]

I have recently moved from Paradox to Microsoft Access. I have been creating
database for about 12 years, so I am somewhat comfortable with data
normalization procedures. I would not say I am a expert by any means, but I
do try and follow some of the rules to 3NF.
However, I noticed that once I started creating tables in Access, I started
getting a lot of errors in forms when trying to use one-to-one relationships
in forms. I did some searching through the posts and found a lot of advice
against using one-to-one (subclassing). Someone even suggested to me that I
combine everything, well then I would have alot of blanks because the data
was not collected for a particular record. That is not very efficient trying
to explain away null values in your database just because subclassing should
not be used. Can someone help explain and bring me up to par.
Here is my question. First does that not go against trying to elimate
fields that do not relate to the key, is the field always entered, etc.
Here is an example of something that I created that I am having problems with:

I have a database where I am doing surveys for vertebrates.
tblSampleUnits: with fields relating to 1 Sample units such as territory,
quad names, county etc.
MasterSampleUnitId
SampleUnitNo
County
Territory
QuadCode
etc.
tblStations: Each Sample Unit has 7 stations, so detail information is put
into the this table about each station.
StationId
MasterSampleUnitId
StationNo
Utme
Utmn
Township
Range
Elevation etc.

Next is a table where I put the vegetation information. This is sometimes
done at random stations, but this survey is only conducted once, and it is
not conducted at all the stations. So I have this as one-to-one from
tblStations to tblVegPlots
tblVegPlots:
StationId
VegDate
WHRTYpe
etc.

Is this an incorrect way of doing tables in Access. Maybe I am set in my
ways and I would appreciate any advice to a better solution. I come across
this situation with alot of databases that I create.

 

[Roger Carlson]

Actually subclassing is a perfectly valid reason for using one-to-one (1:1)
relationships. Most of the times we hear about 1:1 relationships is when
people haven't normalized their database properly. In most of these cases,
the solution is to bring all the fields into one table (or radically change
their design).

The best way to implement a 1:1 in a form is with a form/subform just like
you would a 1:M relationship. That is, the RecordSource for the main form
is the main table (tblStations) and the subform's RecordSource is your
related table (tblVegPlots). The subform control will take care of putting
the primary key value into the foreign key of the "child" table. Since the
subform can only hold one record, I usually display it in Form View, rather
than in Datasheet or Continuous View.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L