?
Solved

Advice on Access 2007 Database Design

Posted on 2009-05-12
8
Medium Priority
?
929 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:Hillyman
  • 3
  • 2
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24370669
Hillyman,

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
0
 
LVL 85
ID: 24370682
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.

0
 

Author Comment

by:Hillyman
ID: 24378441
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!
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24379552
LSM is better at this topic that I am.

I will let you continue with him to avoid any conusion.

;-)

Jeff
0
 
LVL 85
ID: 24380881
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

0
 

Author Comment

by:Hillyman
ID: 24381486
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).  In addition, on the request form the specify the counties to search in.  Each county can have either zero or many results, as you can see from the table tied to Results.  Also, every result has costs that must be logged (one that we charge them and the actual cost of doing the search for us).

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
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 24383458
Well ... a Company actually makes this Request. A Requestor would simply work for a Company (that is, you wouldn't expect payment for your services from the Requestor, but instead from the Company). So you'd need a Company table, and a Requestor table:

tCompany
------------------
lCompanyID
sCompanyName
etc etc

tRequestor (or tEmployee, or whatever)
----------------------
lRequestorID
lCompanyId
sFirstName
sLastName
etc etc

As to Requests, a Company will send you a Request for an Investigation. This Investigation will entail a Subject, which may be a Company or Individual:

tInvestigation
------------------
lInvestigationID
lCompanyID
sInvDescription
dStartDate
etc etc

tLocations
------------------
lLocationID
sLocDesc
etc etc

tInvestigationLocations
----------------------------------
lInvLocId
lInvID
lLocationID

You'll also need to store info on the person/business you're investigating. This is where the difficulty could come in, since you're dealing with different "entities". You need to store different information based on the "type" of subject you're dealing with. You can do this several ways; here's one method you could use, which is a "master/detail" sort of setup, where you store details in a "name:value" sort of setup ... for example, if this were a Doctor, you might store their various aliases in the Detail table, along with their license numbers, etc.

tSubjects
----------------------------
lSubjectID
sSubjecttype (person, business, etc)
sSubjectName

tSubjectDetails
-------------------------------
lSubDetailsID
lSubID
sDetailType
sDetailData
sDetailComments

tInvSubjects
-------------------------------
lInvSubID
lInvID
lSubID

As far as results:

lResults
-------------------
lResultID
lInvID
lLocationID (if you need to tie the results to a specific location)
sDescription
etc etc


0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question