Table/relationship check please.

[melaniecarr23]

I have a client table which has all our patients listed in it. Doc would like me to write up a "calendar" for the year (which WILL change). Basically, he would like to have various tasks done or checked for our patients each week in different departments.

I have created the following tables:

tbl_actions action_type action_lookup
id (key) id (key) patient_id (key)
type_id type action_id (key)
name date
calculation completed (check)

Once the patient is entered into the computer, I will need to run a calculation to populate the action_lookup table with records for each action using the calculation stored in the action table.

Before I go creating things and having to change them because I didn't set it up right, I was hoping someone could check this out. Is there a better way to set this up than how I have?

What will happen after the records are created is a query will be run on action_lookoup date and show a list of patients for the next week and what needs to be done by which staff.

I hope I gave enough information. Thanks!

 

[misscrf]

Here are the things I would change. Name and Date are system constants ( or something like that) and should not be used for object or field names.


tblActions tblActionType tblActionLookup
ActionsId (key) ActionTypeId (key) PatientId (key)
ActionType ActionType ActionType ActionName ActionDate
Calculation Completed (yes/no)



Other stuff:

I dont see a patient table, but I see a patient ID. Does it make sense to have a patient id as the primary key to an action lookup table? What is the point of the action lookup?


ActionType in the tblActions and tblActionLookup are just the field names I am using, it is a lookupwizard to the actiontype, pulling its ID and creating the relationship for me.

Last thing:

http://www.utteraccess.com/forums/s...r=373096&page=0&view=expanded&sb=5&o=&fpart=1

check that out. Everything you need to know to do what you are doing now. You are absolutely right. Get the tables normalized first. you will thank yourself when you end up knee deep in the db!

good rules of thumb are on the link on that page for "NormalizingADatabase"

Good luck and let us know if you have more questions.

I hope this is of some help to you.

misscrf

It is never too late to become what you could have been ~ George Eliot