Hillyman
asked on
Advice on Access 2007 Database Design
I have been asked to assist an investigations company in revamping their medical investigations database and business process. The process goes something like this: They get a fax from a customer with the information they need to do the investigation. Major topics include: Requestor Info | Type of Service Requested | Location and Time Frame to Search (sometimes multiple) | Subject Info (doctor name, address, alias, license, etc.). They enter some of this info into a single Access table, then use a typewriter and a pen to enter the results onto a triplicate form. It's really a nightmare, and especially so because some of their big clients have asked for paperless results. As of now, they haven't even been logging the results into the database.
They seem to want me to create a huge table with a ton of fields. I don't think this is what I should do, but I am inexperienced in creating complicated relational databases. I will post a couple screenshots of what they have, and what I have created so far in the hopes that ya'll can point me in the right direction or let me know if I'm on the right track. The relationships seem to work, but creating forms for data entry when the data is spread between a few related tables has proven to be above my skill set. I need some help please.
I will include a form I created to try and replace the old paper triplicate form they were using. I created it in Livecycle Designer and had hoped to somehow link it to the Access DB. I think it will help outline the situation. The top half of the form is the info we get via fax. The bottom half is the info the investigator responds with.
Thank you so much for taking the time to help me. I have gotten really frustrated while developing this solution, especially since I think I spent a lot of time learning tech that doesn't directly apply. Times winding down on my completion date and I freaked.
1.Before.jpg
2.My.Ideas.To.Change.jpg
3.The.Adobe.Designer.Form.jpg
They seem to want me to create a huge table with a ton of fields. I don't think this is what I should do, but I am inexperienced in creating complicated relational databases. I will post a couple screenshots of what they have, and what I have created so far in the hopes that ya'll can point me in the right direction or let me know if I'm on the right track. The relationships seem to work, but creating forms for data entry when the data is spread between a few related tables has proven to be above my skill set. I need some help please.
I will include a form I created to try and replace the old paper triplicate form they were using. I created it in Livecycle Designer and had hoped to somehow link it to the Access DB. I think it will help outline the situation. The top half of the form is the info we get via fax. The bottom half is the info the investigator responds with.
Thank you so much for taking the time to help me. I have gotten really frustrated while developing this solution, especially since I think I spent a lot of time learning tech that doesn't directly apply. Times winding down on my completion date and I freaked.
1.Before.jpg
2.My.Ideas.To.Change.jpg
3.The.Adobe.Designer.Form.jpg
It seems you're on you way to correctly normalizing the data, but without knowing more about it it's pretty tough to give concrete advice regarding this. I'm not sure how your Locations table is related to your Results table, however, and it seems that relationship would be incorrect.
As to your UI - typically, a Master/Child relationship such as your Contracts-Results relationships would be shown through a Master/Detail form. You really need to get the table structure nailed down before you really even start on the UI, however.
As to your UI - typically, a Master/Child relationship such as your Contracts-Results relationships would be shown through a Master/Detail form. You really need to get the table structure nailed down before you really even start on the UI, however.
ASKER
I read some suggested articles on data normalization and relational design, but I still have some problems getting my head wrapped around the proper design.
1) When we receive a investigation request from a client, they specify either a doctor or a company to investigate (see "Search Parameters" section of form above). The doctors, and sometimes even the companies, often have multiple names or aliases. I could create a Doctors table and a Companies table, but when I start to enter field names like AKA1 and AKA2 I break the normalization rules. So should I create an Aliases table? What's more, they often give us multiple addresses for these companies and doctors which muddles things even more.
2) About the locations (see "Area and Time Frame to Investigate" section on form above). When we get a investigation request, our client often specifies more than one county they want searched. The investigator does his thing and finds all results for each county (there can be none or many). If I create a Requests or Orders table and enter reqCounty1 (ie Los Angeles), reqTime1 (ie 2000-2009), reqCounty2 (San Francisco), reqTime2 (2005-2009), etc. - I start breaking the normalization rules. This is why I had the Locations table. For every request, there can be many locations, and every location can have many results.
I am so upset by this it is unreal. I can normally pick things up rather quickly, and this is giving me a really hard time. I will not give up until I figure this out, and I appreciate any and all input.
One of the problems is that the existing database is not my design. That huge list of fields you see above, the "Contacts" table (which should actually be "Requests", was not created by me, and it has been in use for about three years. Of all the fields you see in the list above, only above 15 are being used, and those are being horribly abused. There are field/value pairs like this:
Subject - "Jekyl, Hyde, MD (aka: Hyde Jekyl, MD)"
Address - "1) 458 Database Dr., Duarte, CA 2) 759 Frompton Ave., Dustin, NV"
ZIP - "1) 84589 2) 54163"
I am going to try and outline the business process the database is to serve a little better, then start a design from scratch to post and get feedback on. I could use some advice on the whole doctors/aliases thing and anything else you might think pertinent in the meantime though. Thanks!
1) When we receive a investigation request from a client, they specify either a doctor or a company to investigate (see "Search Parameters" section of form above). The doctors, and sometimes even the companies, often have multiple names or aliases. I could create a Doctors table and a Companies table, but when I start to enter field names like AKA1 and AKA2 I break the normalization rules. So should I create an Aliases table? What's more, they often give us multiple addresses for these companies and doctors which muddles things even more.
2) About the locations (see "Area and Time Frame to Investigate" section on form above). When we get a investigation request, our client often specifies more than one county they want searched. The investigator does his thing and finds all results for each county (there can be none or many). If I create a Requests or Orders table and enter reqCounty1 (ie Los Angeles), reqTime1 (ie 2000-2009), reqCounty2 (San Francisco), reqTime2 (2005-2009), etc. - I start breaking the normalization rules. This is why I had the Locations table. For every request, there can be many locations, and every location can have many results.
I am so upset by this it is unreal. I can normally pick things up rather quickly, and this is giving me a really hard time. I will not give up until I figure this out, and I appreciate any and all input.
One of the problems is that the existing database is not my design. That huge list of fields you see above, the "Contacts" table (which should actually be "Requests", was not created by me, and it has been in use for about three years. Of all the fields you see in the list above, only above 15 are being used, and those are being horribly abused. There are field/value pairs like this:
Subject - "Jekyl, Hyde, MD (aka: Hyde Jekyl, MD)"
Address - "1) 458 Database Dr., Duarte, CA 2) 759 Frompton Ave., Dustin, NV"
ZIP - "1) 84589 2) 54163"
I am going to try and outline the business process the database is to serve a little better, then start a design from scratch to post and get feedback on. I could use some advice on the whole doctors/aliases thing and anything else you might think pertinent in the meantime though. Thanks!
LSM is better at this topic that I am.
I will let you continue with him to avoid any conusion.
;-)
Jeff
I will let you continue with him to avoid any conusion.
;-)
Jeff
You'd have an Investigations table. Since an Investigation could encompass a person/company with multiple names, you'll need some way to relate all those names to an Investigation. This would require 3 tables:
tInvestigations
------------------
lInvestigationID
sInvestigationname
dStartDate
etc etc
tClientNames
---------------------
lClientID
sClientName
etc etc
tInvestigationClients
--------------------------
lInvClientID
lInvID
lClientID
Since a client could have multiple locations, the same logic would apply:
tLocations
---------------------
lLocationID
lClientId
sLocationname
sLocationAddress
etc etc
so you can now store multiple clients for an Investigation, and you can store multiple Locations for each Client
You now must store results, apparently based on a Location (?) If that's true, then build another table:
tResults
----------------
lResultID
lInvID
lLocationID
dResultDate
sResult
etc etc
So for each location, you can store multiple Results. A Location is related to a Client, but I'd suspect a Client could be involved in more than one Investigation, hence the need to store InvID and LocationID
tInvestigations
------------------
lInvestigationID
sInvestigationname
dStartDate
etc etc
tClientNames
---------------------
lClientID
sClientName
etc etc
tInvestigationClients
--------------------------
lInvClientID
lInvID
lClientID
Since a client could have multiple locations, the same logic would apply:
tLocations
---------------------
lLocationID
lClientId
sLocationname
sLocationAddress
etc etc
so you can now store multiple clients for an Investigation, and you can store multiple Locations for each Client
You now must store results, apparently based on a Location (?) If that's true, then build another table:
tResults
----------------
lResultID
lInvID
lLocationID
dResultDate
sResult
etc etc
So for each location, you can store multiple Results. A Location is related to a Client, but I'd suspect a Client could be involved in more than one Investigation, hence the need to store InvID and LocationID
ASKER
Thank you very much LSM. I can hear the hinges on the iron door starting to squeak as the door leading into the room of understanding opens up ever so slowly. :) Since we've come this far, can you please take a look at my diagram as I explain it a little? I'll start from the top left and move down:
We have companies (Customers), who in turn have employees (Requestors), who fax us Requests for Investigations. The form tied to Requests is a sketch of the info they send. The target is usually a Doctor (who often have one or two aliases, as well as one or two license numbers), but is also sometimes an Entity (business/company/whatev).
With that in mind, do you have any thoughts as to the titles / relationships of the tables?
I've gotta walk someone to her car real quick and I'll be back to post a list of tables I've been thinking about, working on.
Really LSM, you don't know how much I appreciate the help. I really need to just sit down with someone who has experience in this. I really want to keep this contract and do it right so they can take on more customers and make a profit.
process.jpg
We have companies (Customers), who in turn have employees (Requestors), who fax us Requests for Investigations. The form tied to Requests is a sketch of the info they send. The target is usually a Doctor (who often have one or two aliases, as well as one or two license numbers), but is also sometimes an Entity (business/company/whatev).
With that in mind, do you have any thoughts as to the titles / relationships of the tables?
I've gotta walk someone to her car real quick and I'll be back to post a list of tables I've been thinking about, working on.
Really LSM, you don't know how much I appreciate the help. I really need to just sit down with someone who has experience in this. I really want to keep this contract and do it right so they can take on more customers and make a profit.
process.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
In a nutshell:
The main table must be "Normalized" in order to work in a True relational database.
Meaning each of thoes "repeating fileds" " will be brought out into it's own table (Results 1A, Results1B, ...ect)
If not, then you will have to add more fields each tme you add a new "Result"
Again, this is an in-efficient design.
Perhaps another Expert can go into more detail, but basically you need to "Normalize" this table into seperate tables then relate these tables.
Here is a great reference:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719
If it is crunch time, then you might want to think about partnering with an Access developer who can work closely with you on this.
1. To get it done in a timely manner
2. To teach you the concepts of Database design and normalization.
JeffCoachman