Stuck on tables....

[Liam O'Grady]

I made a database to track hearings of an administrative tribunal. I have
two main tables (people and decisions). All other tables are lookup tables.
In the decisions table I need to show which board member sat on the hearing.
I have a lookup table for the board members. There are twenty four board
members. A hearing has usually two board members, but sometimes three. So
for the tblDecisions... I have three columns labeled BM1, BM2 and BM3.

I would like to be able to query the database to see which board members did
which hearings, and to be able to query out the track record of each board
member.

The problem I have run into is that when I create the relationships, access
won't let me create a one to many with referential integrity for each of the
board member columns.

Is there a better way to achieve what I have done? Or a correct way to do
what I am trying to do?

TIA...
Nick.

 

[Vincent Johns]

I agree with what Chris O. said, but have a couple of additional
suggestions (which see below).

Nick,

One of the rules of database design ("Database Normalization: 1st
Normal Form"), says not to have repeating columns. The column
arrangement, "BM1, BM2, BM3," is what is causing the problem.




This is a normal part of MS Access processing.




way to do




What you really want is:

Boardmembers:
BoardmemberID INTEGER -- PK
FName TEXT(48)
LName TEXT(48)

Hearings:
HearingID INTEGER -- PK
HearingDate DATETIME

HearingBoardmembers
HearingBoardmemberID INTEGER -- PK
BoardmemberID INTEGER -- FK to Boardmembers
HearingID INTEGER -- FK to Hearings (HearingID)


The HearingBoardmembers table will show you exactly who officiated.

SELECT H1.HearingID
,B1.LName & ", " & B1.FName
FROM (HearingBoardmembers AS HB1
INNER JOIN
Hearings AS H1
ON HB1.HearingID = H1.HearingID)
INNER JOIN
Boardmembers AS B1
ON HB1.BoardmemberID = B1.BoardmemberID
ORDER BY H1.HearingID
,B1.LName & ", " & B1.FName

(My apologies, the above is untested; but any errors should be
typos, the idea is the correct one.)

For more information you can check out the following links:

---------------------------

Database Normalization:

Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
(I quite like this whole site, since it has a handy menu on the
right
describing many important aspects of database normalization and
modeling.)
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

Trying to follow as closely as possible Chris's names, I set up example
Tables as follows:

[Boardmembers] Table Datasheet View:

BoardMemberID FName LName
------------- ------ ---------
-1635280246 Tom Jefferson
590227011 Dick Nixon
658303944 Harry Truman

[Hearings] Table Datasheet View:

HearingID Date
----------- ----------
-1520052426 12/27/2005
-775858049 12/21/2005

Thinking that the position number might be important, I added that as a
field, setting an index on [Position] and [HearingID] to disallow
duplicate combinations (e.g., no two "BM2" records on the same hearing).

[HearingBoardmembers] Table Datasheet View:

HearingBoard Position BoardMemberID HearingID
MembersID
------------ -------- ------------- -----------
-1729256032 1 658303944 -1520052426
-1399595743 2 590227011 -1520052426
-352188805 3 658303944 -1520052426
1721407138 2 590227011 -775858049
2034149460 1 -1635280246 -775858049

I modified Chris's Query to report not the [HearingID] but the date. If
the [HearingID] is meaningful to users of the database, that would be
good to include, but if not, I think some other value (date or some
person's name) would be better. I also stuck in a [BoardPosition] field
in the Query, to reflect the new [HearingBoardmembers].[Position] field,
in addition to what Chris included.

[Q_Member_History] SQL:

SELECT H1.Date, B1.LName & ", " & B1.FName AS Name,
"BM" & [Position] AS BoardPosition
FROM (HearingBoardmembers AS HB1
INNER JOIN Hearings AS H1
ON HB1.HearingID = H1.HearingID)
INNER JOIN Boardmembers AS B1
ON HB1.BoardMemberID = B1.BoardMemberID
ORDER BY H1.Date, B1.LName & ", " & B1.FName;

Results look like this for the sample data:

[Q_Member_History] Query Datasheet View:

Date Name BoardPosition
---------- --------------- -------------
12/21/2005 Jefferson, Tom BM1
12/21/2005 Nixon, Dick BM2
12/27/2005 Nixon, Dick BM2
12/27/2005 Truman, Harry BM1
12/27/2005 Truman, Harry BM3

Now, if you wish, you can summarize them with a Crosstab Query such as
this one:

[Q_Member_History_Crosstab] SQL:

TRANSFORM Count(Q_Member_History.Date) AS CountOfDate
SELECT Q_Member_History.Name
FROM Q_Member_History
GROUP BY Q_Member_History.Name
PIVOT Q_Member_History.BoardPosition;

The results would look like this:

[Q_Member_History_Crosstab] Query Datasheet View:

Name BM1 BM2 BM3
-------------- --- --- ---
Jefferson, Tom 1
Nixon, Dick 2
Truman, Harry 1 1

Probably you would want to show something besides how many times each
person served as "BM2" on a board, or you might want to filter the
results by a time period during which the boards met, etc., but the
structure of any such Query would be something similar to this.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Chris2]

I made a database to track hearings of an administrative tribunal. I have
two main tables (people and decisions). All other tables are lookup tables.
In the decisions table I need to show which board member sat on the hearing.
I have a lookup table for the board members. There are twenty four board
members. A hearing has usually two board members, but sometimes three. So
for the tblDecisions... I have three columns labeled BM1, BM2 and

BM3.

Nick,

One of the rules of database design ("Database Normalization: 1st
Normal Form"), says not to have repeating columns. The column
arrangement, "BM1, BM2, BM3," is what is causing the problem.

I would like to be able to query the database to see which board members did
which hearings, and to be able to query out the track record of each board
member.

The problem I have run into is that when I create the relationships, access
won't let me create a one to many with referential integrity for each of the
board member columns.

This is a normal part of MS Access processing.

Is there a better way to achieve what I have done? Or a correct way to do
what I am trying to do?

What you really want is:

Boardmembers:
BoardmemberID INTEGER -- PK
FName TEXT(48)
LName TEXT(48)

Hearings:
HearingID INTEGER -- PK
HearingDate DATETIME

HearingBoardmembers
HearingBoardmemberID INTEGER -- PK
BoardmemberID INTEGER -- FK to Boardmembers
HearingID INTEGER -- FK to Hearings (HearingID)


The HearingBoardmembers table will show you exactly who officiated.

SELECT H1.HearingID
,B1.LName & ", " & B1.FName
FROM (HearingBoardmembers AS HB1
INNER JOIN
Hearings AS H1
ON HB1.HearingID = H1.HearingID)
INNER JOIN
Boardmembers AS B1
ON HB1.BoardmemberID = B1.BoardmemberID
ORDER BY H1.HearingID
,B1.LName & ", " & B1.FName

(My apologies, the above is untested; but any errors should be
typos, the idea is the correct one.)


For more information you can check out the following links:

---------------------------

Database Normalization:

Basics:

About.com
http://databases.about.com/od/specificproducts/a/normalization.htm


Intermediate:

MySQL's website:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html


Advanced:

Wikipedia:
http://en.wikipedia.org/wiki/Database_normalization


Very Advanced:

University of Texas:
(I quite like this whole site, since it has a handy menu on the
right
describing many important aspects of database normalization and
modeling.)
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html


Sincerely,

Chris O.

 

[mscertified]

Yes, there is a better way, you need a separate table for the hearing
members. Relate this table to the Hearing table. You will have 1-3 rows in
the hearing members table for each hearing record.
Tables:
(1)Hearing (list of hearings)
HearingID
Date etc.
(2)Hearing member (what members attended what hearings)
HearingID
MemberID
(3)Member (all potential members)
MemberID
Name etc.
(4)Decision (decisions made by each hearing)
HearingID
Decision

-Dorian

 

[Vincent Johns]

Dorian's design would work OK, but you might want to combine the
[Hearing] and [Decision] Tables into just one Table, since the keys are
the same in both. I expect that there are some additional fields that
could also be included there.

Information, if you have any, about what one member did at one hearing
(records of votes, for example) would go into the [Hearing member] Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[mscertified]

Since a hearing could result in multiple decisions you would not want to do
that.

-Dorian

Dorian's design would work OK, but you might want to combine the
[Hearing] and [Decision] Tables into just one Table, since the keys are
the same in both. I expect that there are some additional fields that
could also be included there.

Information, if you have any, about what one member did at one hearing
(records of votes, for example) would go into the [Hearing member] Table.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Yes, there is a better way, you need a separate table for the hearing
members. Relate this table to the Hearing table. You will have 1-3 rows in
the hearing members table for each hearing record.
Tables:
(1)Hearing (list of hearings)
HearingID
Date etc.
(2)Hearing member (what members attended what hearings)
HearingID
MemberID
(3)Member (all potential members)
MemberID
Name etc.
(4)Decision (decisions made by each hearing)
HearingID
Decision

-Dorian

:

 

[Liam O'Grady]

Vincent and Chris,
This has been very informative. Vincent, you have nailed it right on the
head. I do need to query hearings by date. ID will mean nothing to anyone.
I am going to Hawaii for 14 days and (to my wife's objection) I am taking my
laptop. I hope to be able to think through all of this without the
distractions of work and phone calls. The database is to track parole
hearings. I need to be able to track everything about the hearing. Who,
when, where, why, what, date granted parole, date actually released, date
suspended, post suspension hearing date, type of crime the offender was
involved in, type of addiction the offender had, type of hearing (full
parole, day parole, post suspension hearing, parole to out of state, porole
by exception, etc...), what region, what institution, when suspended... what
condition was violated, who were the sitting board members, what visitors
attended, what lawyers, what media, was crystal meth involved?, Hearing
outcome code (grant, deny, postpone, revoke, reinstate....etc),

Here is my existing structute: One table and 14 lookup tables.
tblDecisions - draws from offender table and many of the
lookup tables
tlkpAdditionType - ID, Addiction type
tlkpBrdMem - ID, Lastname, Firstname, address, email, phone
tlkpCVReason - ID, Condition Violation Reason
tlkpDecisionCodes - ID, Decision Code, Decision Code Description
tlkpDecisionType - ID, Decision Type (APP, PSH, APR etc)
tlkpEthnicity - ID, [range of ethnicity]
tlkpFamilyStatus - ID, Family Status (married, divorced,
seperated, common law, etc)
tlkpGangs - ID, Gang name
tlkpGender - ID, Gender
tlkpHearingPurpose - ID, Hearing Purpose (FP, DP, PBE etc....)
tlkpInstitution - ID, Institution
tlkpOffender - ID, CSNumber, Lastname, firstname, photo,
gender, ethnicity etc...
tlkpRegion - ID, Region
tlkptypeofCrime - ID, Crime description

----

From what you are describing, I should add the following table to conform
with normalization rules:

tblHearings
tblHearingBoardMembers

I wonder if I should also make a table for suspensions?
tblSuspensions

About 40% of paroled offenders are suspended at some point. Many of them
are reinstated, but some are not.

Nick.

I agree with what Chris O. said, but have a couple of additional
suggestions (which see below).

Chris2 wrote:

Vincent Wrote:
Trying to follow as closely as possible Chris's names, I set up example
Tables as follows:

[Boardmembers] Table Datasheet View:

BoardMemberID FName LName
------------- ------ ---------
-1635280246 Tom Jefferson
590227011 Dick Nixon
658303944 Harry Truman

[Hearings] Table Datasheet View:

HearingID Date
----------- ----------
-1520052426 12/27/2005
-775858049 12/21/2005

Thinking that the position number might be important, I added that as a
field, setting an index on [Position] and [HearingID] to disallow
duplicate combinations (e.g., no two "BM2" records on the same hearing).

[HearingBoardmembers] Table Datasheet View:

HearingBoard Position BoardMemberID HearingID
MembersID
------------ -------- ------------- -----------
-1729256032 1 658303944 -1520052426
-1399595743 2 590227011 -1520052426
-352188805 3 658303944 -1520052426
1721407138 2 590227011 -775858049
2034149460 1 -1635280246 -775858049

I modified Chris's Query to report not the [HearingID] but the date. If
the [HearingID] is meaningful to users of the database, that would be good
to include, but if not, I think some other value (date or some person's
name) would be better. I also stuck in a [BoardPosition] field in the
Query, to reflect the new [HearingBoardmembers].[Position] field, in
addition to what Chris included.

[Q_Member_History] SQL:

SELECT H1.Date, B1.LName & ", " & B1.FName AS Name,
"BM" & [Position] AS BoardPosition
FROM (HearingBoardmembers AS HB1
INNER JOIN Hearings AS H1
ON HB1.HearingID = H1.HearingID)
INNER JOIN Boardmembers AS B1
ON HB1.BoardMemberID = B1.BoardMemberID
ORDER BY H1.Date, B1.LName & ", " & B1.FName;

Results look like this for the sample data:

[Q_Member_History] Query Datasheet View:

Date Name BoardPosition
---------- --------------- -------------
12/21/2005 Jefferson, Tom BM1
12/21/2005 Nixon, Dick BM2
12/27/2005 Nixon, Dick BM2
12/27/2005 Truman, Harry BM1
12/27/2005 Truman, Harry BM3

Now, if you wish, you can summarize them with a Crosstab Query such as
this one:

[Q_Member_History_Crosstab] SQL:

TRANSFORM Count(Q_Member_History.Date) AS CountOfDate
SELECT Q_Member_History.Name
FROM Q_Member_History
GROUP BY Q_Member_History.Name
PIVOT Q_Member_History.BoardPosition;

The results would look like this:

[Q_Member_History_Crosstab] Query Datasheet View:

Name BM1 BM2 BM3
-------------- --- --- ---
Jefferson, Tom 1
Nixon, Dick 2
Truman, Harry 1 1

Probably you would want to show something besides how many times each
person served as "BM2" on a board, or you might want to filter the results
by a time period during which the boards met, etc., but the structure of
any such Query would be something similar to this.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Liam O'Grady]

I see your point Vincent. However, the way it works is the Board members
deliberate and if they both agree on a decision then the hearing stands and
the decision is rendered. It might be a grant to parole or a denial of
parole. However, if they are deadlocked, then a third boardmember reviews
the hearing and casts the tiebreaker.

Nick.

 

[Vincent Johns]

I didn't do a full analysis, but I have a comment on your names.
Several Tables include a field called [ID], which I suppose is a primary
key in each case. What I usually do with such keys is to give them
names that include the name of the Table. For example, in
[tlkpAdditionType], I might call the primary key that identifies a
record there [tlkpAdditionType_ID] instead of just [ID]. Then, in other
Tables using this as a foreign key, the name [tlkpAdditionType_ID] for
the foreign key would make it obvious which Table the key belongs in.
(I'd also put a Lookup property on that foreign key.)

A couple of the Tables are a bit puzzling. For example, in your
[tlkpGender], if you intend that to refer to sex, there are only
approximately two choices, so a 1-letter name (e.g., "M" or "F") would
suffice and would not require looking up anything. Here's a case in
which I don't recommend setting a lookup property. Even if you have
half a dozen choices, a 1- or 2-letter abbreviation might suffice,
especially if you can come up with meaningful values.

Some of your Tables contain values only one of which might apply to one
offender (the person's sex would be one such, though I didn't actually
see that listed), but in others, such as [tlkpAdditionType], there might
be two or more. If the latter, you'd need another Table linking the two
(many-to-many relationship); one record in this linking Table would
identify a person and one of a set of addictions, plus possibly some
kind of status ("controlled via medication", "latent", "uncontrollably
rampant", &c.), or a reference to source material certifying that the
addiction exists in this person, &c.

I didn't understand your question about [tblHearings] and
[tblHearingBoardMembers] and [suspensions]. Chris2's
[tblHearingBoardMembers] Table was intended to let you record, in each
of its records, the involvement of one board member in one hearing, and
you could also include other related information, such as how that
member voted at that hearing. I expect that [tblHearingBoardMembers]
would contain 2 or 3 records (one per member) linked to each record in
[tblHearings].

Since Access is an RDBMS, it's fairly flexible, and I suggest that you
try populating your Tables with a few sample data and seeing if you can
get meaningful results. Try to cover all the likely possibilities. You
can always add fields later if you need to consider more information.
You might even learn that some of the information you have recorded does
you no good, so you can omit it and save yourself some work.

In Hawaii, I suggest you use the laptop only in moderation (such as
while you're waiting in airports, &c.) and out of sight of your wife,
though you might keep handy a notebook in which to jot down ideas as
they occur to you. Enjoy your trip, and happy New Year!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Vincent and Chris,
This has been very informative. Vincent, you have nailed it right on the
head. I do need to query hearings by date. ID will mean nothing to anyone.
I am going to Hawaii for 14 days and (to my wife's objection) I am taking my
laptop. I hope to be able to think through all of this without the
distractions of work and phone calls. The database is to track parole
hearings. I need to be able to track everything about the hearing. Who,
when, where, why, what, date granted parole, date actually released, date
suspended, post suspension hearing date, type of crime the offender was
involved in, type of addiction the offender had, type of hearing (full
parole, day parole, post suspension hearing, parole to out of state, porole
by exception, etc...), what region, what institution, when suspended... what
condition was violated, who were the sitting board members, what visitors
attended, what lawyers, what media, was crystal meth involved?, Hearing
outcome code (grant, deny, postpone, revoke, reinstate....etc),

Here is my existing structute: One table and 14 lookup tables.
tblDecisions - draws from offender table and many of the
lookup tables
tlkpAdditionType - ID, Addiction type
tlkpBrdMem - ID, Lastname, Firstname, address, email, phone
tlkpCVReason - ID, Condition Violation Reason
tlkpDecisionCodes - ID, Decision Code, Decision Code Description
tlkpDecisionType - ID, Decision Type (APP, PSH, APR etc)
tlkpEthnicity - ID, [range of ethnicity]
tlkpFamilyStatus - ID, Family Status (married, divorced,
seperated, common law, etc)
tlkpGangs - ID, Gang name
tlkpGender - ID, Gender
tlkpHearingPurpose - ID, Hearing Purpose (FP, DP, PBE etc....)
tlkpInstitution - ID, Institution
tlkpOffender - ID, CSNumber, Lastname, firstname, photo,
gender, ethnicity etc...
tlkpRegion - ID, Region
tlkptypeofCrime - ID, Crime description

----

From what you are describing, I should add the following table to conform
with normalization rules:

tblHearings
tblHearingBoardMembers

I wonder if I should also make a table for suspensions?
tblSuspensions

About 40% of paroled offenders are suspended at some point. Many of them
are reinstated, but some are not.

Nick.

I agree with what Chris O. said, but have a couple of additional
suggestions (which see below).

Chris2 wrote:

Vincent Wrote:
Trying to follow as closely as possible Chris's names, I set up example
Tables as follows:

[Boardmembers] Table Datasheet View:

BoardMemberID FName LName
------------- ------ ---------
-1635280246 Tom Jefferson
590227011 Dick Nixon
658303944 Harry Truman

[Hearings] Table Datasheet View:

HearingID Date
----------- ----------
-1520052426 12/27/2005
-775858049 12/21/2005

Thinking that the position number might be important, I added that as a
field, setting an index on [Position] and [HearingID] to disallow
duplicate combinations (e.g., no two "BM2" records on the same hearing).

[HearingBoardmembers] Table Datasheet View:

HearingBoard Position BoardMemberID HearingID
MembersID
------------ -------- ------------- -----------
-1729256032 1 658303944 -1520052426
-1399595743 2 590227011 -1520052426
-352188805 3 658303944 -1520052426
1721407138 2 590227011 -775858049
2034149460 1 -1635280246 -775858049

I modified Chris's Query to report not the [HearingID] but the date. If
the [HearingID] is meaningful to users of the database, that would be good
to include, but if not, I think some other value (date or some person's
name) would be better. I also stuck in a [BoardPosition] field in the
Query, to reflect the new [HearingBoardmembers].[Position] field, in
addition to what Chris included.

[Q_Member_History] SQL:

SELECT H1.Date, B1.LName & ", " & B1.FName AS Name,
"BM" & [Position] AS BoardPosition
FROM (HearingBoardmembers AS HB1
INNER JOIN Hearings AS H1
ON HB1.HearingID = H1.HearingID)
INNER JOIN Boardmembers AS B1
ON HB1.BoardMemberID = B1.BoardMemberID
ORDER BY H1.Date, B1.LName & ", " & B1.FName;

Results look like this for the sample data:

[Q_Member_History] Query Datasheet View:

Date Name BoardPosition
---------- --------------- -------------
12/21/2005 Jefferson, Tom BM1
12/21/2005 Nixon, Dick BM2
12/27/2005 Nixon, Dick BM2
12/27/2005 Truman, Harry BM1
12/27/2005 Truman, Harry BM3

Now, if you wish, you can summarize them with a Crosstab Query such as
this one:

[Q_Member_History_Crosstab] SQL:

TRANSFORM Count(Q_Member_History.Date) AS CountOfDate
SELECT Q_Member_History.Name
FROM Q_Member_History
GROUP BY Q_Member_History.Name
PIVOT Q_Member_History.BoardPosition;

The results would look like this:

[Q_Member_History_Crosstab] Query Datasheet View:

Name BM1 BM2 BM3
-------------- --- --- ---
Jefferson, Tom 1
Nixon, Dick 2
Truman, Harry 1 1

Probably you would want to show something besides how many times each
person served as "BM2" on a board, or you might want to filter the results
by a time period during which the boards met, etc., but the structure of
any such Query would be something similar to this.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[TC]

Vincent Johns wrote:

in your [tlkpGender], if you intend that to refer to sex,

Gender is M or F. Sex is Y or N

Once, I seriously considered whether to add a special-case message, for
where the user entered Y or N (instead of M or F), in a gender/sex
field. Luckily, professionalism prevailed ...

TC

 

[Vincent Johns]

in your [tlkpGender], if you intend that to refer to sex,

Gender is M or F. Sex is Y or N

Har, har.

I suppose you're right about the M or F -- except that, with gender,
it's "masculine" vs. "male", or "feminine" vs. "female", and there's no
value of sex corresponding to "common".

In stupid questionnaires asking me to specify my "gender" (when they're
evidently asking for something else), I'm often tempted to choose a
random answer according to whether I happen to feel sensitive,
assertive, or brainless at the time. I figure if they can't express
themselves clearly, they deserve what they get. (But I avoid doing that
on government forms -- not much intelligence goes into those.)

Once, I seriously considered whether to add a special-case message, for
where the user entered Y or N (instead of M or F), in a gender/sex
field. Luckily, professionalism prevailed ...

TC

Too bad -- that would have been fun.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.