One to One Relationships

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Jamie Collins]

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.

--

 

[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

 

[Jamie Collins]

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.

--

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Jamie Collins]

One way to handle a lot of dates is to put them all into their own table.

tbl_Dates
DateID (PK)
RecordedDate
DateDescriptorID (FK)

Sorry but this is a fallacy. This design flaw is so famous it has a
name ('one true lookup table'), an acronym (OTLT) and an acronym
nickname (MUCK).

Basically, you are taking attributes from multiple domains and lumping
them together, merely because they are of the same data type.

From a practical viewpoint, it will make data integrity constraints

(SQL DDL) considerable harder to write e.g. how would you enforce the
business rule (law of nature) that fieldwork start date cannot occur
before business end date? Ditto queries (SQL DML).

For details see:

http://www.dbazine.com/ofinterest/oi-articles/celko22

Jamie.

--

 

[Guest]

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.

 

[Jamie Collins]

other than "committing spreadsheet," I don't think I have had
another error that actually has a name and an acronym and a nickname.

Another design flaw to be aware of is called 'Entity Attribute Value',
EAV for short (I can't remember its nicknames). Google it.

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

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.

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.

A great approach and a commendable attitude.

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]

 

[Jamie Collins]

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

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

Jamie.

--

 

[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]

 

[Guest]

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.

 

[Jamie Collins]

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?

How far would you take your previous approach? You could have one table
for each data type. Or you could use a MEMO column and put all of your
data into one table a la EAV.

Basic data modelling principles would have you model attributes by
entity type rather than data type. The names you have used would
indicate a Fieldwork entity with StartDate and EndDate attributes.
Implementation of your logical model into SQL is another matter, where
you have to make practical decisions ...

I've been pondering this one and I can't come up with anything much
better than my first post to this thread, where I showed that splitting
the one table solution into three reduced the number of nullable
columns and made constraints easier to write (and therefore maintain).

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.

I you would like to post a scenario I would be happy to look at it. You
may be best starting a new thread to increase you chances of attracting
a real SME <g>.

Jamie.

--