One to One Relationships

[Matt K.]

I thought I was getting a grasp on the whole table/relationship design, but
the more I read the more I feel like I am spinning my wheels.
QUESTION
What is the benefit of having several tables with 1:1 relationships vs. 1
table with all the data pertaining to that table in it??? I know every
situation is different, here's a little about mine...

Basically, I am trying to track a property survey (not a questionaire type)
through from start to finish. A plat (map) is made of each survey. Many
things happen along the way to the survey- by 4 different Departments.
Initially I created a tblSurveys that had 73 fields in it. Most of the fields
were basically dates that certain things happened to the Survey(record) as it
moved "down the line" of Departments.
For example,
FieldworkStartDate
FieldworkEndDate
FieldworkETC
ReviewReceiveDate
ReviewCriticalDate
ReviewFinalDate
ReviewETC
SurveyRecordedDate
Then I thought "would it be better to break out what each Department does to
the Survey?" into tables with 1:1 relationships with tblSurveys??....

tblSurveys
SURVEYid (AutoNumber) PK
SURVEYinfo....

tblDEPT1
SURVEYid (number) FK
all the things that happen to the survey in this dept.

tblDEPT2
SURVEYid (number) FK
all the things that happen to the survey in this dept.

etc. for each Dept.

Of course this is not all the tables in the db, but this is where I am
having trouble deciding on table design.
Maybe I have not given enough info here, but can give more if a thread gets
going.
Any thoughts???

 

[Douglas J. Steele]

Rather than one table per department, have a total of three tables:
tblSurveys, tblDeptSurvey and tblDepts, where tblDeptSurvey is essentially
what you've shown for tblDept1, tblDept2 etc., except that it also has a
field to indicate which department it represents, and tblDepts has one row
per department.

 

[Matt K.]

Bear with me, I am trying to re-learn data organization... you guessed it I'm
trying to get a huge spreadsheet into something more workable.
So, are you describing 2- one to many relationships; tblDeptSurvey being a
junction table since
one survey--many depts
many depts--one survey
???

 

[Matt K.]

OOPS
one survey--many depts
one dept --many surveys

 

[tina]

correct - two one-to-many relationships, with each parent table on the "one"
side, and the child junction table on the "many" side. this is the standard
resolution to a many-to-many relationship in Access.

hth

 

[Matt K.]

Jamie,

That is a lot for me to chew on...but I think I am getting a somewhat
clearer picture of the situation. I think maybe I was falling into the
scenario of creating to few tables for ease of creating my queries and forms
on the front end (And also because I am used to spreadsheets). I was
discovering what you pointed out at the end of your post when I started
splitting "things" up into MANY tables. The data I am dealing with is complex
(for me) in terms of how it all relates and all the different scenarios that
can be created.
Here's my summary(understanding) of your answer to my question: The benefit
of having 4 tables (tblFieldwork, tblReview, etc.) with 1:1 relationships to
tblSurveys is that this way I don't have a bunch of "empty" fields in one
large flat table, because there will only be a record in one of the 4
corresponding table when that process (related to the table) begins. Am I on
the right track here?

Thank you for taking the time to respond in such detail. I appreciate it.

Matt

 

[mnature]

Just to muddle you a bit more: You seem to have a lot of dates to keep track
of. One way to handle a lot of dates is to put them all into their own table.

tbl_Dates
DateID (PK)
RecordedDate
DateDescriptorID (FK)

Then you need a table of descriptors (which I suppose will be the names of
the different surveys), so you know what each date is for:

tbl_DateDescriptors
DateDescriptorID (PK)
DateDescriptorText
DepartmentID (FK)

Then your description of your needs gets a little hard to follow. So I am
assuming here that each unique date, as described by the Descriptor, belongs
to some department, so you need to have a table showing those:

tbl_Departments
DepartmentID (PK)
DepartmentName

Remember, however, that Jamie Collins is much more experienced than I am in
designing databases, and his advice is probably more sound. I just like to
point out that sometimes there can be several ways of approaching database
design.

 

[mnature]

Oh, oops. You probably want to track which survey a date belong to, also. I
should really read things through thoroughly before hitting the "Post" button.

tbl_Dates
DateID (PK)
RecordedDate
DateDescriptorID (FK)
SurveyID (FK)

tbl_Surveys
SurveyID (PK)
SurveyInfo

tbl_DateDescriptors
DateDescriptorID (PK)
DateDescriptorText
DepartmentID (FK)

tbl_Departments
DepartmentID (PK)
DepartmentName

 

[mnature]

This is precisely why I come here to post possible solutions for others. It
is not to try and help them, but to try and hone my own abilities. Getting
slapped upside the head for posting something stupid is an excellent learning
opportunity, for which I am quite grateful. So far, I believe I have
uncovered nearly every possible way of doing something wrong. Statistically
speaking, then, I must be getting closer to perfection.

However, other than "committing spreadsheet," I don't think I have had
another error that actually has a name and an acronym and a nickname. Plus,
it has been a while since someone had to jump in and tell me I was just plain
wrong. Thanks for the save, Jamie.

 

[John Vinson]

Note that "committing spreadsheet", an Access convention term (John
Vinson?), is often mistaken around here for 'intentionally
denormalized' but that's another discussion...

My consulting resume includes a line "Judicious denormalization, only
when necessary" <g>

John W. Vinson[MVP]

 

[John Vinson]

So *did* you coin the phrase "committing spreadsheet", John?

Jamie.

I'd have to do a google search to see, but if I recall aright, yes.

John W. Vinson[MVP]

 

[mnature]

Getting back to the original posting, I'd like to ask the SME's (Jamie and
John, in particular) how they would sort out a lot of dates, such as are
being tracked for Matt's application. Would you place dates such as
FieldworkStartDate and FieldworkEndDate, etc., into various tables, keeping
that particular naming convention? When I gave my advice, I was thinking in
terms of getting these labels (FieldworkStart, FieldworkEnd) out of the field
names, and having them designated from lookup tables. Do you leave these
labels in the field names, in order to be able to enforce the business rule
of certain dates having to occur before other dates?

I get the feeling that I'm missing some part of the logic of this, and would
appreciate either some small discussion, or perhaps a link to an article
about this.