One to One Relationships

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???

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Matt K." wrote in message
...
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???

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
???

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Matt K." wrote in message
...
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???

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

"Matt K." wrote:

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
???

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Matt K." wrote in message
...
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???

Matt K. wrote:
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???

You should look for things that are indicative of a design flaw.

At one end of the spectrum you have the flaw of using too few tables.
Symptoms of this problem include many nullable columns and constraints
being more difficult to define.

For example, consider these columns where each survey_ID can only ever
have one of each (i.e. 1:1 relationships):

survey_ID
FieldworkStartDate
FieldworkEndDate
ReviewReceiveDate

Let's assume all columns are nullable, except the key column survey_ID
of course, so that a survey can exist before fieldwork has started and
that a review cannot 'receive' until fieldwork has ended (that
fieldwork cannot end before it has started is a law of nature).

Putting that into one table:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL UNIQUE,
FieldworkStartDate DATETIME,
FieldworkEndDate DATETIME,
ReviewReceiveDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
CHECK (FieldworkEndDate = ReviewReceiveDate),
CHECK (NOT(FieldworkStartDate IS NULL AND FieldworkStartDate IS NOT
NULL)),
CHECK (NOT (FieldworkStartDate IS NULL AND ReviewReceiveDate IS NOT
NULL)),
CHECK (NOT (FieldworkEndDate IS NULL AND ReviewReceiveDate IS NOT
NULL))
);

I think there are too many nullable columns here, and those last three
constraints are a bit ugly yet necessary with the chosen design if we
are to ensure data integrity. In other word, I think the design is
flawed.

Now let's try splitting things up:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE Fieldwork (
survey_ID INTEGER NOT NULL PRIMARY KEY
REFERENCES Surveys (survey_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FieldworkStartDate DATETIME DEFAULT NOW() NOT NULL,
FieldworkEndDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
UNIQUE (survey_ID, FieldworkEndDate)
);

CREATE TABLE Reviews (
survey_ID INTEGER NOT NULL PRIMARY KEY,
FieldworkEndDate DATETIME NOT NULL,
FOREIGN KEY (survey_ID, FieldworkEndDate)
REFERENCES Fieldwork (survey_ID, FieldworkEndDate)
ON DELETE CASCADE
ON UPDATE CASCADE,
ReviewReceiveDate DATETIME DEFAULT NOW() NOT NULL,
CHECK (FieldworkEndDate = ReviewReceiveDate)
);

Now we have only one nullable column, FieldworkEndDate, to indicate
that Fieldwork is ongoing; FieldworkStartDate is required, therefore
fieldwork has not started no row should be present at all. Also, those
ugly constraints have gone, effectively replaced by making columns
required and using DRI (foreign keys etc).

At the other end of the spectrum you have the flaw of 'attribute
splitting', where many tables are used in the design (SQL DDL) that
makes queries etc (SQL DML) harder to write e.g. you are forever using
OUTER JOINs to join the tables back together and test for nulls,
effectively reconstructing the first design flaw but, worse, doing so
on the fly. This design flaw can be subtler and harder to identify (and
correct) because it is your later SQL DML that indicates that your
earlier SQL DDL was wrong.

HTH,
Jamie.

--

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." wrote in message
...
OOPS
one survey--many depts
one dept --many surveys

"Matt K." wrote:

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
???

"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Matt K." wrote in message
...
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???

Matt K. wrote:
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.

Maybe this article could help:

"Hollywood Couples" by Celko
http://www.intelligententerprise.com/010101/celko.jhtml

Jamie.

--

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

"Jamie Collins" wrote:


Matt K. wrote:
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???

You should look for things that are indicative of a design flaw.

At one end of the spectrum you have the flaw of using too few tables.
Symptoms of this problem include many nullable columns and constraints
being more difficult to define.

For example, consider these columns where each survey_ID can only ever
have one of each (i.e. 1:1 relationships):

survey_ID
FieldworkStartDate
FieldworkEndDate
ReviewReceiveDate

Let's assume all columns are nullable, except the key column survey_ID
of course, so that a survey can exist before fieldwork has started and
that a review cannot 'receive' until fieldwork has ended (that
fieldwork cannot end before it has started is a law of nature).

Putting that into one table:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL UNIQUE,
FieldworkStartDate DATETIME,
FieldworkEndDate DATETIME,
ReviewReceiveDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
CHECK (FieldworkEndDate = ReviewReceiveDate),
CHECK (NOT(FieldworkStartDate IS NULL AND FieldworkStartDate IS NOT
NULL)),
CHECK (NOT (FieldworkStartDate IS NULL AND ReviewReceiveDate IS NOT
NULL)),
CHECK (NOT (FieldworkEndDate IS NULL AND ReviewReceiveDate IS NOT
NULL))
);

I think there are too many nullable columns here, and those last three
constraints are a bit ugly yet necessary with the chosen design if we
are to ensure data integrity. In other word, I think the design is
flawed.

Now let's try splitting things up:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE Fieldwork (
survey_ID INTEGER NOT NULL PRIMARY KEY
REFERENCES Surveys (survey_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FieldworkStartDate DATETIME DEFAULT NOW() NOT NULL,
FieldworkEndDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
UNIQUE (survey_ID, FieldworkEndDate)
);

CREATE TABLE Reviews (
survey_ID INTEGER NOT NULL PRIMARY KEY,
FieldworkEndDate DATETIME NOT NULL,
FOREIGN KEY (survey_ID, FieldworkEndDate)
REFERENCES Fieldwork (survey_ID, FieldworkEndDate)
ON DELETE CASCADE
ON UPDATE CASCADE,
ReviewReceiveDate DATETIME DEFAULT NOW() NOT NULL,
CHECK (FieldworkEndDate = ReviewReceiveDate)
);

Now we have only one nullable column, FieldworkEndDate, to indicate
that Fieldwork is ongoing; FieldworkStartDate is required, therefore
fieldwork has not started no row should be present at all. Also, those
ugly constraints have gone, effectively replaced by making columns
required and using DRI (foreign keys etc).

At the other end of the spectrum you have the flaw of 'attribute
splitting', where many tables are used in the design (SQL DDL) that
makes queries etc (SQL DML) harder to write e.g. you are forever using
OUTER JOINs to join the tables back together and test for nulls,
effectively reconstructing the first design flaw but, worse, doing so
on the fly. This design flaw can be subtler and harder to identify (and
correct) because it is your later SQL DML that indicates that your
earlier SQL DDL was wrong.

HTH,
Jamie.

--

Just to muddle you a bit mo 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.

"Matt K." wrote:

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

"Jamie Collins" wrote:


Matt K. wrote:
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???

You should look for things that are indicative of a design flaw.

At one end of the spectrum you have the flaw of using too few tables.
Symptoms of this problem include many nullable columns and constraints
being more difficult to define.

For example, consider these columns where each survey_ID can only ever
have one of each (i.e. 1:1 relationships):

survey_ID
FieldworkStartDate
FieldworkEndDate
ReviewReceiveDate

Let's assume all columns are nullable, except the key column survey_ID
of course, so that a survey can exist before fieldwork has started and
that a review cannot 'receive' until fieldwork has ended (that
fieldwork cannot end before it has started is a law of nature).

Putting that into one table:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL UNIQUE,
FieldworkStartDate DATETIME,
FieldworkEndDate DATETIME,
ReviewReceiveDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
CHECK (FieldworkEndDate = ReviewReceiveDate),
CHECK (NOT(FieldworkStartDate IS NULL AND FieldworkStartDate IS NOT
NULL)),
CHECK (NOT (FieldworkStartDate IS NULL AND ReviewReceiveDate IS NOT
NULL)),
CHECK (NOT (FieldworkEndDate IS NULL AND ReviewReceiveDate IS NOT
NULL))
);

I think there are too many nullable columns here, and those last three
constraints are a bit ugly yet necessary with the chosen design if we
are to ensure data integrity. In other word, I think the design is
flawed.

Now let's try splitting things up:

CREATE TABLE Surveys (
survey_ID INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE Fieldwork (
survey_ID INTEGER NOT NULL PRIMARY KEY
REFERENCES Surveys (survey_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FieldworkStartDate DATETIME DEFAULT NOW() NOT NULL,
FieldworkEndDate DATETIME,
CHECK (FieldworkStartDate FieldworkEndDate),
UNIQUE (survey_ID, FieldworkEndDate)
);

CREATE TABLE Reviews (
survey_ID INTEGER NOT NULL PRIMARY KEY,
FieldworkEndDate DATETIME NOT NULL,
FOREIGN KEY (survey_ID, FieldworkEndDate)
REFERENCES Fieldwork (survey_ID, FieldworkEndDate)
ON DELETE CASCADE
ON UPDATE CASCADE,
ReviewReceiveDate DATETIME DEFAULT NOW() NOT NULL,
CHECK (FieldworkEndDate = ReviewReceiveDate)
);

Now we have only one nullable column, FieldworkEndDate, to indicate
that Fieldwork is ongoing; FieldworkStartDate is required, therefore
fieldwork has not started no row should be present at all. Also, those
ugly constraints have gone, effectively replaced by making columns
required and using DRI (foreign keys etc).

At the other end of the spectrum you have the flaw of 'attribute
splitting', where many tables are used in the design (SQL DDL) that
makes queries etc (SQL DML) harder to write e.g. you are forever using
OUTER JOINs to join the tables back together and test for nulls,
effectively reconstructing the first design flaw but, worse, doing so
on the fly. This design flaw can be subtler and harder to identify (and
correct) because it is your later SQL DML that indicates that your
earlier SQL DDL was wrong.

HTH,
Jamie.

--

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