Relationships to connect several tables

[Guest]

I work for a Mental Health Agency and have been asked to develop an
assessemnt form for "Early Psychosis" patients. I have created the tables --
the main table being the "MPI" number (unique to each patient) and a visit
number, as the patient may have one MPI #, but several assessments. All other
tables are to be linked to this one - 18 tables in all - depicting different
aspects of the assessment. My problem is how to create the relationship
between not only the MPI #, but to each particular assessment. AS of now,
each of the tables has only an auto number for the primary key. Thanks.

 

[Jeff Boyce]

Jane

Based only on what you've described so far, it sounds like your data could
be structured much like a spreadsheet ... and this is not a good thing in a
relational database!

If you'll provide a bit more explanation of what kinds of data (data
elements) is being stored in what kind of tables, the newsgroup may be able
to offer alternate suggestions that will make your (and Access') job
easier...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Sorry about the confusion. This is the first DB I have created. Anyway, to
give you an idea, here are some of the tables:

Client Number - the main MPI number and the visit number
TblClient - demographics
TblAssessemnt - Clinician name, psychiatrist name, location of app't, etc
TbleSignsSymptoms - Sign and symptoms of psychosis - several option/drop
down lists here
TblMedicalHx - medical hx of client
TblFamilyHx - psychiatric family hx

and the list goes on ...
Fields are text, option boxes, y/n fields,etc.
We prefer a database so as to keep stats for our own Mental Health Program
and also numbers for Ministry of Health

I have created a form for Clinicians to use when they first see the client
and enter all this info into. So I don't have a problem with creating the
tables or the forms, just the relationships as all the info entered must
relate with the original MPI number and the particular visit # (e.g. when the
clinician brings up past info, she can see how many times the client has been
in and past info.)
At the moment I am experimenting with making the MPI # and the visit # a
double primary key.
Am I way off base here?

 

[Guest]

The one thing that stands out to my eye is that you state you have already
created the form for the clinician's data entry......but haven't established
the table relationships.....

That would be a cart-before-horse scenario....or there is a
misunderstanding......

As a Form is sourced on a Table or a Query - then these must pre-exist the
Form's creation.......unless you have a totally unbound form.....

An 18 table db is pretty complex. Possibly some of these are acting as
lists...such as the typical 2 letter state abbreviation.......but in any case
you definitely need to clarify all your cross references between tables...and
then build a query that brings all the data together correctly....then make
the form to it.

 

[Smartin]

I work for a Mental Health Agency and have been asked to develop an
assessemnt form for "Early Psychosis" patients. I have created the tables --
the main table being the "MPI" number (unique to each patient) and a visit
number, as the patient may have one MPI #, but several assessments. All other
tables are to be linked to this one - 18 tables in all - depicting different
aspects of the assessment. My problem is how to create the relationship
between not only the MPI #, but to each particular assessment. AS of now,
each of the tables has only an auto number for the primary key. Thanks.

Hi Jane,

In addition to what others have said, my $0.02... It seems you have
already given thought to how things are related and perhaps you are in a
"forest vs. trees" situation.

Of capital importance when designing a database is how you
compartmentalize (or "normalize") the data. Each table should describe
one "entity". You have a patients table, presumably this has information
about people, and specifically not about their visits. Do you also have
a visits table, to record information about visits (specific to a
person), but only having data about, say, time, physician, diagnosis? A
visit could be further decomposed into procedures or services (yet
another table).

The key question is does each table describe or define exactly one
entity (or "thing")?

In your situation I imagine you would need two fairly extensive tables
to records patient and visit information, along with lots of subservient
"helper" tables to tie in procedures and/or services, billing,
locations, referral information, etc. Each of the helper tables relate
back to a parent. A parent table at one level could be a child to
another table.

Sorry if this all sounds a bit gooey. You may want to Google on terms
like "entity relationship model". I think your database will depend on
good understanding of this concept.

Hope this helps.

 

[Jamie Collins]

I don't have a problem with creating the
tables or the forms, just the relationships as all the info entered must
relate with the original MPI number and the particular visit # (e.g. when the
clinician brings up past info, she can see how many times the client has been
in and past info.)
At the moment I am experimenting with making the MPI # and the visit # a
double primary key.
Am I way off base here?

Not at all.

You have a two column compound key of (MPI #, visit #). You have
probably noticed that most examples use a single column, often an
autonumber, to create a 'Relationship' (FOREIGN KEY) between tables.

I'm an advocate of natural keys but it's a volatile subject so I'll
leave you to do your own research -- there's plenty out there. Phrases
to google include "compound key", "natural key", "foreign key"; for
view from the other side, try "surrogate key".

Jamie.

--

 

[Jamie Collins]

Of capital importance when designing a database is how you
compartmentalize (or "normalize") the data. Each table should describe
one "entity".

The key question is does each table describe or define exactly one
entity (or "thing")?

You may want to Google on terms
like "entity relationship model". I think your database will depend on
good understanding of this concept.

You name-checked "entity-relationship model" (ERM) but you neglected
to mention that a table can alternatively model a relationship between
entities. BTW an alternative approach to consider is "object role
modeling" (ORM), arguably a richer model especially as regards
integrity constraints.

Jamie.

--

 

[Jeff Boyce]

Jane

Again, based on what you've posted, it sounds like you have created tables
to allow you to create forms. As suggested elsewhere, this may be
"cart-before-horse" design.

Step back from your computer for a moment, pick up paper and pencil, and
start writing down each of the "things-about-which-you-want-to-keep-data"
(person, place, thing, concept, ...). These need to be collections of facts
that hold together tightly.

For example, a person "entity" (things-about-which...) would have FirstName,
LastName, DOB, but would NOT have AppointmentDate, AppointmentTime,
AppointmentWith. Those facts ("attributes") would seem to belong to an
"Appointment" entity.

When you've sketched out your entities, start drawing lines between them,
showing the relationships. In your situation, it sounds like one patient
can have many appointments. This is a one-to-many relationship. Show this
in your diagram.

When you have these, consider posting back here and asking about your
entity-relationship findings. These will end up being your tables. (and
hopefully, the tables you have already created will be a very close match
.... but don't limit yourself!)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jamie Collins]

Step back from your computer for a moment, pick up paper and pencil, and
start writing down each of the "things-about-which-you-want-to-keep-data"
(person, place, thing, concept, ...).

For example, a person "entity" (things-about-which...) would have FirstName,
LastName, DOB, but would NOT have AppointmentDate, AppointmentTime,
AppointmentWith. Those facts ("attributes") would seem to belong to an
"Appointment" entity.

I thought at first this might relate to the UK but I think it's
Canada. Shame because this is pretty much prescribed (<g>) in the UK
via data standards e.g. the below link should convey the complexity
involved and why the OP's tally of 18 tables should be of no surprise:

http://www.connectingforhealth.nhs....ental_health_minimum_dataset_fr.asp?shownav=1

it sounds like you have created tables
to allow you to create forms. As suggested elsewhere, this may be
"cart-before-horse" design.

Do you mean like when one decides to use bound forms then avoids
databases elements such as parameterized stored procs, table
constraints, transactions, row-level locking, etc because they
'inconvenience' the bound forms approach <g>?

Jamie.

--

 

[Jeff Boyce]

Jamie

One of the stronger arguments I've seen for using (?quasi-)ORM modeling is
that it makes more intuitive sense to the eventual users.


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Thanks to all. Lots of ideas to think about. I can see already where I may
need to restructure.

 

[Smartin]

You name-checked "entity-relationship model" (ERM) but you neglected
to mention that a table can alternatively model a relationship between
entities. BTW an alternative approach to consider is "object role
modeling" (ORM), arguably a richer model especially as regards
integrity constraints.

Jamie.

You are absolutely right. Thanks for filling in the gap.