Pulling It All Together

[S Jackson]

It has been awhile since I designed a db from the bottom up and I am having
trouble pulling it together. I am designing a db to track guardianship
cases. We will need to track the following:

-Region
-County
-County Attorney
-County Clerk
-County Judge
-Ward
-etc.

Each Region contains multiple counties. Each county has multiple county
attorneys, clerks and judges. But each case will reside in one region, one
county, have one ward (with multiple interested parties), one county
attorney, one clerk, and one judge. Here are the tables we have so far (To
simplify matters, I have not necessarily included all of the fields for each
table):

tblCounty
-CountyID
-RegionID
-CountyName

tblCAtty
-CAttyID
-CountyID
-CAName

tblClerks
-ClerkID
-CountyID
-ClerkName

tblJudges
-CourtID
-CountyID
-JudgeName

tblWard
-WardID
-WardName

tblIntParties
-IntPartyID
-WardID
-IntPartyID

How do I chain all this together into a single form. I know how to create a
form to include the Ward information, and then a subform for tblIntParties
(one-to-many relationship), but how do I include all of the county
information. Do I need a bridge table (where I can also include other
miscellaneous case information)?:

tblCases
-CaseID
-CountyID
-RegionID
-CAttyID
-CourtID
-WardID

If so, what does the query look like? My goal is for the user to select a
county from a drop-down which will automatically populate the Region field
and then populate another drop-down for the user to select the CAtty (county
attorney), another drop-down to select the county clerk, etc.

I'm a little lost here. Hopefully, I haven't created too much confusion here
as well.
S. Jackson

 

[tina]

yes, you do need a tblCases. that's the "entity" or "subject" you're really
tracking - guardianship Cases. all the other tables are "supporting" the
integrity of the data entered about a Case, by supplying valid values that
will be used in specific fields. just be sure that *the fields in each
supporting (parent) table describe that entity* (a region, a county, a
clerk) - not a guardianship case.

the relationship of the interested parties is not clear. will any given Case
have one or more specific interested parties that are *specific to that
Case*? or are the interested party(s) specific to a Ward - so that all cases
with a specific ward assignment will have the same interested parties? and
may specific interested parties be linked with more than one Ward? because
there are, ultimately, multiple interested parties involvd with a single
Case, at some point a sub-table will be needed. if you want to describe how
interested parties fit in, in more detail, i'll try to help you define the
correct tables/relationships in that area.

as for form data entry (and putting aside the issue of interested parties
for the moment), you can create a simple form bound to tblCases. for each
field in the table that has a related "supporting" table, create a combo box
and bind the field to it. set the combo box's RowSource to the primary key
field and the "name" field of the supporting table. for example:

in frmCases, add a combo box named cboCounty. set the control's properties
as
ControlSource: CountyID (this is the field in tblCases)
RowSource: SELECT CountyID, CountyName FROM tblCounty ORDER BY CountyName
ColumnCount: 2
ColumnWidth: 0"; 1"
BoundColumn: 1
LimitToList: Yes

you can further control the user's data entry by limiting the available
choices in some of the combo box droplists. for instance, tblCounty has a
RegionID in it. you can set the TabOrder in the form so that the user enters
a region first, in cboRegion. then set the RowSource of cboCounty to only
display counties belonging to that region, as

RowSource: SELECT CountyID, CountyName FROM tblCounty WHERE RegionID =
Forms!frmCases!cboRegion ORDER BY CountyName

add a macro or VBA code to cboCounty's Enter event, to requery the combo
box.
and btw, whatever you do, DON'T put Lookup fields in any of your tables. see
http://www.mvps.org/access/lookupfields.htm for reasons why.

hth

 

[S Jackson]

Thanks Tina:

I've made some changes since my last post. I have removed the Ward table.
The ward information is unique to each case, so I moved the fields to the
case table. Here are the tables I have now:

tblCases:
-CaseID
-WardName
-WardAddy1
-WardAddy2
-WardCity
-WardState
-WardZip
-WardPh
-WardSSN
-WardDOB
-CaseWorkerID
-RegionID
-CountyID
-CourtID
-CAttyID
-ClerkID

tblCounty
-CountyID
-RegionID
-CountyName

tblCAtty
-CAttyID
-CountyID
-CAttyName
-CAttyAddy1
-CAttyAddy2
-CAttyCity
-CAttyState
-CAttyZip
-CattyPh
-CAttyFx

tblClerks
-ClerkID
-CountyID
-ClerkName
-ClerkAddy1
-ClerkAddy2
-ClerkCity
-ClerkState
-ClerkZip
-ClerkPh
-ClerkFx

tblCourts
-CourtID
-CountyID
-CourtName
-JudgeName
-CourtAddy1
-CourtAddy2
-CourtCity
-CourtState
-CourtZip
-CourtPh
-CourtFx
-CourtAdmin
-CourtAdminPh
-CourtAdminFx

tblIntParties
-IntPartiesID
-CaseID
-IntPartyName
-IntPartyRelationship
-IntPartyAddy1
-IntPartyAddy2
-IntPartyCity
-IntPartyState
-IntPartyZip
-IntPartyPh

tblCaseWorkers
-CaseWorkerID
-CaseID
-CaseWorkerAddy1
-CaseWorkerAddy2
-CaseWorkerCity
-CaseWorkerState
-CaseWorkerZip
-CaseWorkerPh
-CaseWorkerFx

tblTasks
-TaskID
-CaseID
-TaskName
-TaskDate
-TaskComplete

tblIndex
-IndexID
-CaseID
-IndexEntry
-IndexDate
-IndexFU

None of the tables have lookup fields. The last fields in tblCases are
Number fields in which I will store the selection made by the user from the
combo box I will put in the form whose source will be the supporting table
(e.g. to select a case worker, the user would select an entry from a combo
box whose source will be tblCaseWorkers, and the selection will be stored in
tblCases.CaseWorkerID) Is this the correct way to do it?

If so, I have another question: I will also want to display the rest of the
information related to the case worker selected (or County Attorney
selected, etc.). Therefore, I will want to place fields from tblCaseWorker
on the form to display this information. So do I base the form off of a
query written like this:

SELECT tblCases.*, tblCaseWorker.*, tblCounty.*, tblCAtty.*, tblCourts.*
FROM ((((tblCases LEFT JOIN tblCaseWorker ON tblCases.CaseWorkerID =
tblCaseWorker.CaseWorkerID) LEFT JOIN tblCAtty ON tblCases.CAttyID =
tblCAtty.CAttyID) LEFT JOIN tblClerks ON tblCases.ClerkID =
tblClerks.ClerkID) LEFT JOIN tblCounty ON tblCases.CountyID =
tblCounty.CountyID) LEFT JOIN tblCourts ON tblCases.CourtID =
tblCourts.CourtID;

I haven't put any information into the db yet, so I haven't tested this
query out yet.

the relationship of the interested parties is not clear. will any given Case
have one or more specific interested parties that are *specific to that
Case*? or are the interested party(s) specific to a Ward - so that all cases
with a specific ward assignment will have the same interested parties? and
may specific interested parties be linked with more than one Ward? because
there are, ultimately, multiple interested parties involvd with a single
Case, at some point a sub-table will be needed. if you want to describe how
interested parties fit in, in more detail, i'll try to help you define the
correct tables/relationships in that area.

Hopefully, the redesign of the tables answers your question. However,
please note that each case has only one ward, but each case have multiple
interested parties - the interested parties belong to the case, not the
ward - does that make sense? I had planned to insert a subform to display
the Interest Parties.

TIA
S. Jackson

My query will look like this:

 

[tina]

a few points:

in any table that includes the name of a person, always use a FirstName
field and a LastName field (and optionally, a field for middle name or
initial). putting an entire name together in one field violates data
normalization rules, and down the line it'll cause headaches, i guarantee.
whenever you need to *display* a full name as a single entity, you can
easily concatenate the fields in a query, form, or report. for example:

FullName: FirstName & " " & LastName
FullName: LastName & ", " & FirstName

tblCaseWorkers stores data that describes case workers. don't include a
CaseID foreign key field in this table. you've already established a correct
link between cases and case workers by putting a CaseWorkerID foreign key
field where it belongs - in tblCases.

if it's possible that one judge may preside over more than one court at any
given time (i don't know anything about the ins-and-outs of the judicial
structure, obviously), then suggest you have a separate table to list judges
(remember, FirstName, LastName). then use the primary key from that table as
a foreign key in tblCourts.

could a single interested party be involved in more than one guardianship
case? for instance, in a situation where three children (brothers and
sisters) are all wards of the court - could the common grandmother of all
three be listed as an interested party in each child's case? if this sort of
scenario is possible, then there is a many-to-many relationship between
tblCases and tblIntParties. you need to create a linking table to resolve
the relationship. remove the CaseID field from tblIntParties and create the
linking table, as

tblCaseIntParties
CaseID (foreign key from tblCases)
IntPartyID (fk from tblIntParties)
you can use both fields as a combination primary key for this table, or
create an autonumber field to use as a surrogate primary key.

if you have a series of standard tasks that are completed for cases, suggest
you use tblTasks to identify those tasks, using the TaskID and TaskName fiel
ds, and perhaps a TaskDescription field. then create a tblCaseTasks to track
what task was completed for each case, and when, as

tblCaseTasks
CaseID (foreign key from tblCases)
TaskID (fk from tblTasks)
DateAssigned (or date begun, perhaps?)
DateCompleted

None of the tables have lookup fields.
excellent!

The last fields in tblCases are Number fields in which I will store the
selection made by the user from the combo box I will put in the form
whose source will be the supporting table (e.g. to select a case
worker, the user would select an entry from a combo box whose
source will be tblCaseWorkers, and the selection will be stored in
tblCases.CaseWorkerID) Is this the correct way to do it?

very good - that's it, exactly.

I will also want to display the rest of the information related to
the case worker selected (or County Attorney selected, etc.).
Therefore, I will want to place fields from tblCaseWorker
on the form to display this information. So do I base the form off
of a query written like this:

<SQL snipped>

yes, that should work for display purposes, but you may not be able to enter
data in the form if you base it on such a multi-table query. (i'm not that
great at envisioning the results of "air code" SQL - i usually have to build
the tables and test it.) you might consider basing the form on tblCases, and
then using subforms to display the related data from the supporting tables.
a subform based on tblCaseWorkers, for instance, would be linked to the main
form on the CaseWorkerID field in both forms. (this type of setup is a
reversal of the "standard" use of subforms, which is to enter/display
multiple child records that are related to a single parent record in the
main form. you'll use the "standard" solution to allow entering/displaying
multiple interested parties for each case.)

you're doing a good job setting out your tables/relationships. hopefully the
above will help you tweak the design just a bit to keep it in line with
normalization rules.

hth

 

[John Nurick]

in any table that includes the name of a person, always use a FirstName
field and a LastName field (and optionally, a field for middle name or
initial). putting an entire name together in one field violates data
normalization rules, and down the line it'll cause headaches, i guarantee.

FirstName, MiddleName and LastName bring headaches of their own because
not all names fit that pattern, and one thing worse than a
non-normalized database is a database that is the wrong shape for the
data. A few examples:

Zhou Xiaochuan (Zhou is his family name)
Madonna (is that a first name or a last name?)
Robin Lane Fox (Lane is part of the last name)
The Duke of Devonshire (his family name is Cavendish)

Handling all the possible elements of multicultural personal names is
too much like hard work for my taste, and also requires whoever's
entering the names to understand them. So when I'm designing a database
that will have to handle "non-standard" names I tend to treat them as
"atomic" and store as many different versions of each name as required,
for instance:

IndexName: for sorting, searching, etc., e.g. "Roe, Jane E"
AddressName: for addressing mail etc., e.g. "Ms Jane E Roe"
Salutation: e.g. "Cutie-Pie", "Jane" or "Ms Roe" as appropriate.

 

[tina]

well, i agree that handling "non-standard" names is a problem in database
development. but i don't think that i'd resort to storing the same names
multiple times. that doesn't really solve the problem, it simply moves the
burden from the developer to the end user.

that's why database development is as much art as science, i guess - there
are so many areas where there is no perfect solution, and each developer
must choose the one that seems to him or her to be the best compromise.

 

[S Jackson]

Tina:

a few points:

in any table that includes the name of a person, always use a FirstName
field and a LastName field (and optionally, a field for middle name or
initial). putting an entire name together in one field violates data
normalization rules, and down the line it'll cause headaches, i guarantee.
whenever you need to *display* a full name as a single entity, you can
easily concatenate the fields in a query, form, or report. for example:

FullName: FirstName & " " & LastName
FullName: LastName & ", " & FirstName

Good point. However, in most of the tables I will not need to break down
the name field into first name and last name because I will never need to do
any kind of query based on the name field.

tblCaseWorkers stores data that describes case workers. don't include a
CaseID foreign key field in this table. you've already established a correct
link between cases and case workers by putting a CaseWorkerID foreign key
field where it belongs - in tblCases.

Oops - yes, you are right.

if it's possible that one judge may preside over more than one court at any
given time (i don't know anything about the ins-and-outs of the judicial
structure, obviously), then suggest you have a separate table to list judges
(remember, FirstName, LastName). then use the primary key from that table as
a foreign key in tblCourts.

At this point I do not believe that a judge will preside over more than one
court, but thank you for bringing this point up. It is something I am
definitely going to have to check into before going any further.

could a single interested party be involved in more than one guardianship
case? for instance, in a situation where three children (brothers and
sisters) are all wards of the court - could the common grandmother of all
three be listed as an interested party in each child's case? if this sort of
scenario is possible, then there is a many-to-many relationship between
tblCases and tblIntParties. you need to create a linking table to resolve
the relationship. remove the CaseID field from tblIntParties and create the
linking table, as

tblCaseIntParties
CaseID (foreign key from tblCases)
IntPartyID (fk from tblIntParties)
you can use both fields as a combination primary key for this table, or
create an autonumber field to use as a surrogate primary key.

I am impressed with how thoroughly you have gone over this. However, if
there are three wards (all siblings), there will only be one case on the
docket, not three separate cases for each ward (BUT, I will definitely
confirmed this before moving on). Therefore there will not be a
many-to-many relationship between Cases and Interested Parties. Thank
goodness because those relationships kick my butt - they are hard for me to
get my head around.

if you have a series of standard tasks that are completed for cases, suggest
you use tblTasks to identify those tasks, using the TaskID and TaskName fiel
ds, and perhaps a TaskDescription field. then create a tblCaseTasks to track
what task was completed for each case, and when, as

tblCaseTasks
CaseID (foreign key from tblCases)
TaskID (fk from tblTasks)
DateAssigned (or date begun, perhaps?)
DateCompleted

Again, I am so impressed with how thoroughly you have gone over things.
When I originally set up this table it was not for a standard set of tasks.
However, there may be a standard set of deadlines that may apply to all
cases, so thank you for telling me how to set that up.

very good - that's it, exactly.

<SQL snipped>

yes, that should work for display purposes, but you may not be able to enter
data in the form if you base it on such a multi-table query. (i'm not that
great at envisioning the results of "air code" SQL - i usually have to build
the tables and test it.) you might consider basing the form on tblCases, and
then using subforms to display the related data from the supporting tables.
a subform based on tblCaseWorkers, for instance, would be linked to the main
form on the CaseWorkerID field in both forms. (this type of setup is a
reversal of the "standard" use of subforms, which is to enter/display
multiple child records that are related to a single parent record in the
main form. you'll use the "standard" solution to allow entering/displaying
multiple interested parties for each case.)

I will give your suggestion a try. I wasn't comfortable including that many
tables in my query.

you're doing a good job setting out your tables/relationships. hopefully the
above will help you tweak the design just a bit to keep it in line with
normalization rules.

hth

THANK YOU AGAIN FOR ALL OF YOUR HELP!

Shelly J.

 

[tina]

comments inline.

sort

...if there are three wards (all siblings), there will only be one case on the
docket, not three separate cases for each ward

whoa. let's take another look at tblCases. in an earlier post, you said:

I've made some changes since my last post. I have removed the Ward table.
The ward information is unique to each case, so I moved the fields to the
case table.

but if there may in fact be multiple wards in one case, you DO need a
tblWards, as

tblWards
WardID (pk)
CaseID (fk from tblCases)
FirstName
LastName
DOB
SSN
Address
etc.

and you'll need to remove those fields from tblCases, of course. you also
need to consider whether siblings assigned to one case might have separate
interested parties. children with the same mother and different fathers
would have separate paternal grandparents. is it important that the
interested parties be identified with the specific child they have an
interest in, in those cases? if so, then the relationship would be between
tblWards and tblIntParties, *not* tblCases and tblIntParties.

go a step further, and consider whether a single child might be assigned two
separate case numbers at some point. how about a child who becomes a ward of
the court, and whose status is decided and the case meets whatever criteria
required for it to be closed. then the child becomes a ward of the court
*again* for some reason. will that previous case be re-opened or
re-activated, regardless of how much time has passed? or will the child be
assigned to a new case #? if the latter is possible, then a many-to-many
relationship between tblCases and tblWards may be appropriate, which would
require a linking table. there are other ramifications of such a
relationship, so you need to consider it carefully.

suggest you also give further consideration to tblIntParties, also. what
about that grandmother whose grandchildren from more than one son/daughter
are wards of the court? might those children be assigned to separate cases,
since they're from different nuclear families? again, that implies a
many-to-many relationship between tblCases and tblIntParties (or between
tblWards and tblIntParties - see above comments).

Again, I am so impressed with how thoroughly you have gone over things.

the first task of a developer is to thoroughly analyze the business process
that the database must support. it requires careful thought and sometimes a
lot of research - which is why defining tables/relationships is often
time-consuming and the most difficult part of developing a database. and
the developer who's working with the process (that's you) is in the best
position to do that, which is why it's dangerous to take
tables/relationships suggested by an "outsider" as gospel. just use the
questions i've asked and the points i've raised as a springboard to get your
own thought processes flowing - and keep in mind that you have to build your
database to support not only the "standard" process, but also (to a
reasonable degree) the unusual occurrences, as well. and if you identify
"possible" scenarios that you decide are too rare to fully support, then you
should make sure that your customer (management, or whoever) understands
that those scenarios' oddities *won't* be fully supported, and how they'll
be processed within the standard configuration.

hth

 

[S Jackson]

Thank you Tina for all of your comments and support. You definitely have
raised some serious issues that I need to consider before jumping into this
project.

As you have already guessed, I am very new to all of this. I have
self-taught myself almost all that I know in the last 5 years and have
worked on the development of a few databases here in our office to support
case management. Unfortunately, I have had to make some major changes to
our case management database because of the learning process, but we've all
got to start somewhere, dont' we?

Thanks again. I can see I have a lot more research to do.

Shelly J.

 

[S Jackson]

After checking into whether a case would have multiple wards I found out
that this program will only involve adult wards - not minors. So therefore,
there will only ever be one ward for one case - even in a sibling situation
because the wards are adults. Phew! Thank goodness.

Shelly J.

 

[tina]

comments inline.

Thank you Tina for all of your comments and support. You definitely have
raised some serious issues that I need to consider before jumping into this
project.

As you have already guessed, I am very new to all of this. I have
self-taught myself almost all that I know in the last 5 years and have
worked on the development of a few databases here in our office to support
case management. Unfortunately, I have had to make some major changes to
our case management database because of the learning process, but we've all
got to start somewhere, dont' we?

yes, we do. i started out the same way, self-taught, and of course made a

Thanks again. I can see I have a lot more research to do.

defining tables/relationships (called data modeling) is the most difficult,
and the most important, part of building a database - but you're ahead of
the game because you're willing to invest in the learning process. a good
text to learn data modeling is Database Design for Mere Mortals by Michael
Hernandez. that the text i used when i took a data modeling class several
years ago. you can also find tons of helpful links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

good luck, and come on back to the newsgroups whenever you need a hand with
a specific issue.

hth