[mso] Re: Sorry Very Long Setting up DB ... Naming and Relations Access 2K
- From: "Dian Chapman" <dian@xxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Thu, 2 Jan 2003 12:33:45 -0600
Pardon me for jumping in, but here's an article from TechTrax on Access
normalization that has an explanation and some illustration that might
help?
Databases: Normalizing Access Data
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88
Dian Chapman
Technical Consultant, Instructor,
Microsoft MVP & TechTrax Editor
Word AutoForm/VBA eBook: http://www.mousetrax.com/books.html
Tutorial web site: http://www.mousetrax.com/techpage.html
TechTrax Ezine: http://www.mousetrax.com/techtrax/
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Colli, Anthony G
Sent: Thursday, January 02, 2003 9:01 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K
April-
When it comes time to normalize a database, this is what I generally
do. Take a clean sheet of paper and write out the processes involved.
A patient visits a doctor.
A patient has an insurance provider.
An insurance provider pays a doctor for a patient visit.
A doctor receives payment from an insurance provider.
You can see where this leads. There will be a patient, visit, doctor,
insurance provider, and payments table. The PatientID will have a
foreign key of ProviderID. The Visits table will have a foreign key of
PateintID and DoctorID. The Payments table will have a foreign key of
VisitID.
This works very well to help normalize a database. Once you have all
the tables and relationships plotted out you can then transfer it to
Access to see if it meets your requirements. Once in Access you can add
some development data and write queries and reports to see if you can
retrieve the info in the format you require.
-Anthony
-----Original Message-----
From: 4office [mailto:4office@xxxxxxxxxxxxx]
Sent: Tuesday, December 31, 2002 4:29 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K
James, Thanks for all your help... Hopefully you wont mind me picking
your brain as I do this... Ok you clear some things up for me... but you
did also muddy the water for me... cuz you reminded me that while yes, I
added the ClientID (txtCltID) to each table, I forgot to create an ID
for say the Doctor or Insurance Carrier... and I did not add those
fields to my original Client table... Also, since I just read (from I
think a link you just sent me to mvp.org/access... that my love for
lookup tables is BAD BAD BAD....) What is the easiest and correct way
for the end user to either choose an existing say doctor or add a new
one when looking at the client table/form?? Is it better to just link it
on the form side and not on the table side like I do with lookup
tables??
Ok so that also means that the Table with the Types of service needed,
and the Hours/Days Requested, and the summary/assessment stuff and the
medicare/medcare/ins policy/Group# Really could all be on the original
Client Table with only the name of the Ins Carrier or
Doctors'/Phone/Address on a separate table? I'm I getting this right??
The Responsible Party is it's own table, but only used when they are
not checked (on the Client's Primary Table) as being the same, Right??
Same for the and the Contact party which I should separate from the
Responsible Party, with a check box about whether they are the contact
also...
Remember:
>>The Responsible Party could be the Client but may not be the
Contact Person
Contact Person's will never be the Client but could be the
Responsible party
How do I eliminate redundancy, or can I?? (Those yes/no
Questions?? If
Responsible Party is NOT the Client these fields are required,
if Contact
Name is NOT Responsible Party these fields are required... Ok
then how do I
make sure they don't accidentally check both?? Saying the
Contact is the
Responsible Party who is the Client (the Contact can not be the
Client)<<
On the Hospitalization table:
tblHospitalization
txtCltID (this would be the Primary Key)
dteEnter (This would be the foreign Key)
dteDischarged
cboDisposition (This will be a lookup of choices ie "Recover no more
care needed" "When to Nursing Home" "Expired")
and this should remain a different table since a client (Patient) can go
in and out of the hospital, I'm Learning!
Ok so I have another field that is specific to each client it is their
Ambulation Status (There is only 1 of 5 possible answers) Instead of
creating a lookup table I should just make it a text field and then on
the form create a combo-box with those 5 possible answers, Right? Ok, by
doing this are they limited to only those 5 possible answer or can they
add a sixth if ever there becomes one (Will this now make the list 6
options for the future?)
Am I asking to many questions for one posting... you know the saying,
one question, begets another....
THANKS FOR ALL YOUR HELP, I very much want to get this (right) the first
time... Sorry if I am making you repeat your self... I just want to be
sure that I am digesting what your telling me and not misconstruing what
your saying with my obvious bad habits!
April
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of James LaBorde
Sent: Tuesday, December 31, 2002 3:37 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
April,
You will want to separate the data that will duplicate. Doctors will
likely be their own table since they may have more than one patient.
Any data that is particular to the doctor will go in their table and a
fk will be in the client table listing which doctor they have.
Remember, the particular data has to relate directly to the primary key.
Will the patient see more than one doctor? You may want a visits table
that would be separate from all of the others. You still want your
separate tables if the data will not refer uniquely to the primary key.
The additional tables you have with billing and mailing addresses could
go either way. Insurance carriers again will duplicate across clients
and will likely have data of their own. It would work to have a table
for them and a single reference to it in your client table.
I was merely trying to point out the fact that you can have a large
number of fields in a table that refer specifically to the primary key.
We have a number of tables in our operational database that have over
100 fields per record.
In your question you list separate entities in each question. That data
is important but if you have data specific to those separate entities
you will want to separate them. That way you only have one listing for
each insurance carrier and have minimal problems of misspellings or
typos in the address. Think of all the fields you need to include and
ask yourself what the field refers to. If it refers to the primary key,
then it belongs in the table, if it refers to another field in your
table, it should probably be its own table with a foreign key designated
for it. In your Insurance carriers example, insurance carrier refers to
the primary key of patient, but insurance carrier address refers to
insurance carrier. So you would include an Insurance Carriers table and
have a field in your patients table that referred to the specific
insurance carrier.
I hope I am helping with clearing this up, rather than making the waters
muddier.
James
-----Original Message-----
From: 4office [mailto:4office@xxxxxxxxxxxxx]
Sent: Tuesday, December 31, 2002 11:41 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
James,
Ok then are you saying that it is ok to put ALL the information that I
have broken up on the 7 tables that I listed before (and the approx 5
that I did not spell out) into ONE table, since they are all information
related to the "Client" : Who are they, who will pay, who is their
doctor, who is their insurance carrier....
April
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx]On
Behalf Of James LaBorde
Sent: Tuesday, December 31, 2002 2:07 PM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Sorry Very Long Setting up DB ... Naming and
Relations Access 2K :VSMail mx3
40ffice (sorry, you didn't use your name),
The easy answer to your question is that the limit to the number of
columns in a single table is only limited by size. The best way to
determine if you should break up your tables is to ask yourself if
everything in the table refers to the Primary Key. In the case of your
first table, that would be the client. One thing to remember about your
look-ups, don't embed the data. Make a small table that houses the data
and make sure that somewhere in your application you give a user (an
Admin) the ability to add to that list if necessary. You are correct
about the age being able to be calculated. There are some issues with
calculating age though so be careful. You can make it display in any
format you choose, although it may require that you do a little coding
to get the exact format you wish.
As far as your redundancy issues. You can leave certain fields inactive
and unaccessible until the appropriate box is checked. You are
venturing into territory where you will need the vba coding more and
more though.
I hope this helps. If you have any more specific questions feel free to
ask.
One resource that should be of great help will be
http://www.mvps.org/access/
James
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx
or MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will
also allow you to change your email settings to digest or vacation (no
mail). //www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group,
send a request to mso-moderators@xxxxxxxxxxxxx and you will be sent an
invitation with instructions. Once you are a member of the files group,
you can go here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
