Database designed the best way?

**mitch455** · 03-07-2012, 12:30 PM

In a previous job, I used Access 97 quite regularly. I was adept at designing queries and reports. It's been a few years since I have used it however, and even longer since I actually had to design and create a database. I know the data I'm working with now is an excellent candidate for an access database - we have contract providers most with multiple staff contacts, multiple contracts, and multiple locations. However, not all providers have multiple locations or staff. Some providers are just individuals with one or more contracts.

I think I have the data divided up between appropriate related tables but I would greatly appreciate a more knowledgeable pair of eyes giving it a look-see.

Overview:

tblProviders - This table is sort of the main enchilada. Each provider is one entity, with multiple pieces of additional information. I think Address1 through Phone fields can probably be deleted, and the primary address assigned to the primary contact/location? This table populates the main form (frmProviders) for searching, data entry, etc.

tblStaff - This table populates a tabbed sub-form on the main form. Many providers (about half of 300 total) have multiple staff contacts. Right now, this table includes a lookup column for contact types, but as I understand this is the not the best way to do this, I will probably move these values to a related table.

tblContracts - Each provider has multiple contracts either at the same time or continuously expiring and renewing. This table populates a tabbed subform on the main provider form.

tblContractTypes - This table holds the short list of contract types. I imagine I'll move the contact types to an arrangment like this as well.

tblLocations - For about half the providers, there is only one address. But for the rest, there are multiple locations that either correspond to one staff person, or there is a different staff manager for each location. This table populates a third tabbed subform on the main provider form.

TIA for any advice!
Jackie

**mitch455** · 03-07-2012, 12:36 PM

I forgot to mention that the attached database has half dummy/half actual information in it. I imported an excel spreadsheet and not all the fields have been filled in or cleaned up. Record 254 is a good example of a main contractor with multiple staff and multiple locations.

**orange** · 03-07-2012, 04:11 PM

Forget about which table will populate Form and or subform.

What is the purpose of the database? What does your business" do?
Just describe your setup in plain English, much like you would say to someone who didn't have any knowledge of Access nor your "business".

Here is a link describing the process of figuring out your tables and how they relate

http://www.rogersaccesslibrary.com/T...lationship.zip

**mitch455** · 03-08-2012, 09:40 AM

The database I attached is in mdb format - does it not work? I saved it down from an accdb and when I look at it, it shows that it's an mdb.

Basically, I have a database for tracking contractors, the contracts we hold with them, and the people with whom we work. I work for a government agency and we contract with agencies and individual people for mental health services (psychologists, music therapists, adult foster care home owners, etc.). This is a new responsibility for me. The last contract assistant left about 9 months ago and the position has been vacant ever since. With some recent shifting of responsibilities, I was asked to take this over. The previous person kept an excel spreadsheet that she saved as a new file every month. There are 3 or 4 rows for a lot of contractors since they have multiple contracts or locations. And dozens of excel spreadsheets with slightly different information depending on what had renewed over the last month. It's kinda a mess. Everyone is pretty frustrated with the contracts dept and my supervisor has high expectations for me. I could be the hero or the goat with this database.

Contracts run for 1 to 3 years and have various start and end dates. I will need to be able to run lists of contractors whose contracts are about to expire so we can begin the renewal process. I also need to know where contracts are in process as far as when we sent them out, did we get them back from the contractor, has the executive director signed off.

Some contractors have multiple current contracts but have different managers for each contract, so I also need to be sure we are contacting the right person for each contract.

I've tried to use normalization as much as I know how. For those individual people with whom we have contracts, there are no other staff contacts and no additional locations. But, for at least half of our 300+ contractors, there are multiple staff and/or locations. So the main contractor table (tblProviders) has address information, but those fields are also repeated on in tblLocations.

I've done a lot of thinking about the design and table structure. I've read through many of the forums here, and I went through a couple different online trainings. The whole front side of one of my lateral files is covered with paper. I used a separate sheet for each table I thought I'd need as I divided the data. I have arrows from one sheet to the next to show relations. I have sticky notes with questions for users all over the place. The result is the database I attached. Before I start cleaning up and adding new data, I just want to see if I am headed in the right direction.

Thanks!
Jackie

**orange** · 03-08-2012, 11:06 AM

A few things.

I can assure you that getting your tables and relationships designed to support your business is the most important part of database. Failure to get proper structures, will force work arounds and much unnecessary coding and frustration.

It sounds that you have a working structure, but that you have some misgivings or concerns. Do you have some "thought out" test data ? Is the current database structure causing issues? I don't think it is Normalized, but I am just now looking at the "business the model and database is supporting". I don't think you need to store multiple versions of Address1, Address2City, State, Zip . I think it should be in Location table, then have a junction Table that relates Provider and Location-- say tblProviderLocation. As for contract are contracts made with the Provider or Provider at a specific location?

It seems to me that Contract, in addition, to StartDate and EndDate, may have a dollar value.

I'm trying to get some understanding of how your government agency works. I have worked for a federal government where finance, assistance programs, materiel management and human resources were managed via corporate database. There were some local versions (downloads) of parts on desktops to assist with charts and presentations, but the authority was the corporate database. All updates were done in the corporate environment.

You mentioned

I also need to know where contracts are in process as far as when we sent them out, did we get them back from the contractor, has the executive director signed off.

which implies some sort of business processes that exist in your area. You should identify these processes and what information each needs; which inputs or outputs and reads/updates.

Also you have mentioned some entities -- the things you deal with
Contractors
Contracts
(we contract with) Agencies and Individuals
mental health services
Locations
(contractors since they have )multiple contracts or locations.
Contracts run for 1 to 3 years and have various start and end dates
ContractContact(contacting the right person for each contract.)

I'm not sure what your interest is in the Staff at a Provider Location. I would think having the proper Contract Contact would be the key issue,
Maybe I have misunderstood where Staff fits. (I just wouldn't want to rely on my database to identify staff at a provider facility in general - I have no control over "their staff", but I do want to ensure I have the correct Contact for an active Contract.

In my opinion, a decision as to which software environment you will use should be made, database (Access) or spreadsheet(Excel)

kept an excel spreadsheet that she saved as a new file every month.

There are videos(free) here that may help with

I've tried to use normalization as much as I know how.

**mitch455** · 03-09-2012, 10:10 AM

Originally Posted by orange

It sounds that you have a working structure, but that you have some misgivings or concerns. Do you have some "thought out" test data ? Is the current database structure causing issues? I don't think it is Normalized, but I am just now looking at the "business the model and database is supporting". I don't think you need to store multiple versions of Address1, Address2City, State, Zip . I think it should be in Location table, then have a junction Table that relates Provider and Location-- say tblProviderLocation. As for contract are contracts made with the Provider or Provider at a specific location?

I don't know if the current database is causing issues yet - I just made it.

I've populated it with the information from the latest excel sheet leftover from the last staff person. Record 254 (Residential Options) is a good example of a contractor with multiple staff contacts and multiple locations. However, I haven't quite figured out how to link a contact with a specific location.

In general, contracts are made with a provider, and each provider has a main address. However, as with 254, the provider makes the contract and we have a corporate contact person, but our daily business is with each of the individual homes (locations) which each have a different manager. The other scenario is where an individual person is the provider with whom we have a contract and they live at 123 Street, but their adult foster care home is located at 456 Street. I need to keep track of both locations.

I did have a thought about eliminating the Address1 through Phone fields in the provider table and using the location table to keep track of that, adding a location type of primary for the corporate or preferred mailing address. I do need to somehow link certain staff people to certain addresses for those instances like in record 254, where we have specific staff people tied to specific locations. My supervisor would be more likely to need to contact the main provider staff person, whereas I would be more likely to have to send information to each manager at each specific home location.

Question 1 - I'm uncertain about how to make the locations link to specific people, as well as be linked as they are now to the provider. And if I link it to the person, how do I make the person's whole name show up in a selection box?

Question 2 - I have contract type related to contracts and and the contract type name is what shows up in the combo box. However, the contract type column is on the contracts table. Should it be there? Or should this be something that only shows up on the form?

It seems to me that Contract, in addition, to StartDate and EndDate, may have a dollar value.

Dollar amounts of the contracts are not part of the tracking I do. The amounts of the contracts are complicated, involving approved Medicaid amounts, which vary according to level of care and the whim of the state powers that be. That information is passed off to our billing staff. It is possible that in the future, we may add that piece to the provider database. But that's a thought for the future.

I'm trying to get some understanding of how your government agency works.

Ha! Well if you figure that out, let me know. Really, we operate more like a non-profit health care agency. My responsibility with contracts pretty much extends to tracking their existance. What dates do they start and end and where are we in the renewal process, and providing appropriate contact information when requested. Any information that I need to report on is included by use of the various date fields in the contract table.

I'm not sure what your interest is in the Staff at a Provider Location. I would think having the proper Contract Contact would be the key issue,
Maybe I have misunderstood where Staff fits. (I just wouldn't want to rely on my database to identify staff at a provider facility in general - I have no control over "their staff", but I do want to ensure I have the correct Contact for an active Contract.

And that's it really. I used the word Staff because I had contractor, contract, and contact and it was getting to be too many c-words. Contractor became Provider and Contact became Staff. I'm not keeping track of the staff at provider facilities.

In my opinion, a decision as to which software environment you will use should be made, database (Access) or spreadsheet(Excel)

Oh, we're going with Access. No doubt about it.

I can't view youtube videos here at work, and databaseanswers.org is infected with a virus and it's blocked. I've read through much of the information on normalization and I think if I remove the Address1 through Phone fields from the provider table, I'll pretty much have it in this database. Right?

**orange** · 03-09-2012, 11:04 AM

I am attaching a jpg of how I first saw your Tables and Relationships --- provider01.jpg but there are questions that we have to work through to make it do what you need. (Note: I left the address stuff in all tables in case there is data available and has to be moved.)

It's unfortunate that you are not allowed to access the videos, because you need to get your tables structured to make your task easier and manageable. And videos, at least for me, help guide you through the processes and you can go back and watch over and over.

The link to Rogers library in the last post has excellent material but it is text and not video. Still very well worth reading and understanding the first few topics at least.

My comment and question from the last post was

I don't think you need to store multiple versions of Address1, Address2City, State, Zip . I think it should be in Location table, then have a junction Table that relates Provider and Location-- say tblProviderLocation. As for contract are contracts made with the Provider or Provider at a specific location?

Your point here

I used the word Staff because I had contractor, contract, and contact and it was getting to be too many c-words. Contractor became Provider and Contact became Staff. I'm not keeping track of the staff at provider facilities.

is interesting and shows me a hidden Entity FACILITY
It has to be represented in the Tables and Relationships.

?? Have you tried writing a 2-3 line definition/description of each of these things? It is worth doing. You'd be surprised how little facts arise when you do and then try to relate the different things. It clarifies things and can be valuable in ongoing maintenance and changes, and fo communicating with others.

Question 1 - I'm uncertain about how to make the locations link to specific people, as well as be linked as they are now to the provider. And if I link it to the person, how do I make the person's whole name show up in a selection box?
How to make things show up under different situations/conditions is better left until actual programming. The key is to get the tables designed and related to one another to support your business.

Question 2 - I have contract type related to contracts and and the contract type name is what shows up in the combo box. However, the contract type column is on the contracts table. Should it be there? Or should this be something that only shows up on the form?

Getting various columns to show is a programming issue - telling Access which column you want.Can be left until later.

How do you or your group fit with the billing staff/org?
Does the billing system have the same definitions of these things -- contract, contact..... and are they represented by the same "coding system" that the Access system will use?

It sounds to me you need info about a Provider (mailing address etc) but also info about a "ProviderFacility"/Home/House. It also seems that you need a CorporateContact for each Provider; and a Contact at each ProviderFacility; and a ContractContact.

That's it for now. Will expand this and modify diagram once you provide some feedback.

**orange** · 03-09-2012, 12:07 PM

Here is a revised model based on a few guesses and your last post info.

The multiple Staff tables represent Contact Roles
-Provider Corporate Contact
-Provider Facility Contact
-Contract Contact

some fields may still be redundant, others may be missing, but this was just an update to generate comments.

**mitch455** · 03-09-2012, 12:46 PM

I may be able to view the youtube videos from home this weekend. I do well watching videos but I also get a lot out of text as well. I did go through the Rogers Library stuff and that's what started me on getting rid of the Address1 through Phone fields in Providers and making a Locations table. The information makes sense, I just struggle to apply it without assistance, so thanks for all this.

I think your use of Provider Facility is interchangeable with my Locations. There are not multiple locations and also additional facilities. The facilities are the locations I'm keeping track of. Were you under the impression they were different?

I have been going through the design process making forms as well as tables, but I can see how this is not the best way. I'll set forms aside for now and trust that the way to get information to show up doesn't need to be part of the thought-process for now.

?? Have you tried writing a 2-3 line definition/description of each of these things?

Things being tables? Fields? I did a lot of dialoguing with my now supervisor when I took over this job about exactly what each of the fields on the old spreadsheet were for and what sort of reporting did he want me to provide. I used that to sort of work backward to start figuring out what fields I needed in the database and then assigned them to tables. I didn't get to the line by line definition of the data however.

How do you or your group fit with the billing staff/org?
Does the billing system have the same definitions of these things -- contract, contact..... and are they represented by the same "coding system" that the Access system will use?

Right now, the billing staff use an access database with only the vendor number of the provider (assigned by our accounting software) and the rates (anywhere from 3 to 30 for each provider). I looked at the possibility of developing the provider database from that, but it was so bare bones it really didn't provide much of a start. My statement that it might one day be incorporated was the result of a question posed to my supervisor about what information from the contract needed to be tracked by me? Rates/services? The answer was maybe one day. Things move so slowly here I doubt it will ever be a real concern. I added the ContactType of Billing only as a future placeholder of sorts. I would have no contacts/staff with that value right now.

It sounds to me you need info about a Provider (mailing address etc) but also info about a "ProviderFacility"/Home/House.

Yes, but it seems that holding provider information separate from location/facility is a good idea. Did you have further thoughts about this?

It also seems that you need a CorporateContact for each Provider; and a Contact at each ProviderFacility; and a ContractContact.

I do have a corporate contact for each provider and a contact/staff at most facilties/locations. Sometimes, there is just an individual (corporate contact) who manages multiple locations. Record 200 shows an individual with 3 homes. I do not have a contract contact. All contacts are managed through the corporate contact. Each location will have a contact/staff, be it the corporate contact or a different manager. But contracts are not specific to each location.

Having said that, I can see where if you look at record 200 you can see she has three contracts and three locations. It does appear that each contract is specific to a location. But it you consider record 254, the corporation has one contract covering three locations, and a manager at each location as well as a corporate contact.

I consulted your diagram, but with the contracts not being tied to locations, I've attached a screenshot of what I've come up with. Do I have an extra relationship between tblStaff and tblProviders since both are tied to tblProviderLocation?

relationships.doc

**mitch455** · 03-09-2012, 12:51 PM

Oh, I missed your interim post, but I'll wait to see if you have any thoughts about my thoughts.

**orange** · 03-09-2012, 08:08 PM

Here are some facts to review or adjust.

-A Provider is a company/org that offers mental health services
-Typical mental health services include (psychologists, music therapists, adult foster care home owners, etc.)
-A Provider may have 1 or more Facilities
-A Facility may not be at the same address/location as the Provider's main office
-A Contract is between a Provider and our government agency
-A Contract is in effect for 1-3 years with varied Start and End Dates
-A Provider has 1 Corporate Contact
-Each Facility has a Contact
-Each Contract has a Contact
-A Contact can be the Contact for a Facility and/or Contract and/or Provider

I don't understand the significance of this

But contracts are not specific to each location.

What exactly is the Contract wording? To provide XXX services at Facility AAA and/or BBB.

**mitch455** · 03-12-2012, 09:05 AM

-A Provider is a company/org that offers mental health services: Yes for the most part.
-Typical mental health services include (psychologists, music therapists, adult foster care home owners, etc.) For the most part yes. There are also building lease contracts, fiscal intermediary services contract, audit support, etc.
-A Provider may have 1 or more Facilities Yes
-A Facility may not be at the same address/location as the Provider's main office Yes
-A Contract is between a Provider and our government agency Yes
-A Contract is in effect for 1-3 years with varied Start and End Dates Yes
-A Provider has 1 Corporate Contact Yes
-Each Facility has a Contact Yes, but sometimes it is the corporate contact
-Each Contract has a Contact In a sense yes, but always only ever the corporate contact
-A Contact can be the Contact for a Facility and/or Contract and/or Provider Yes

As far as contract and locations go, I guess they are linked, but it is not always a one-to-one relationship. Some contracts cover multiple locations, and many contracts are not for services provided at a particular location. In your first relationship diagram, staff was linked only to providers, and contracts were part of the Provider/Facility/Contract junction. I think staff and contracts should be switched in that model. Contracts are only ever thought of as belonging to the provider. And if staff are not linked to their particular facilities, how would I ever pull that data out for mailing?

I can see where you are going in the second model, but I think that would cause a LOT of over lap. Many, many providers have one person, one facility, and one contract. Having multiple Staff tables would cause a great deal of duplicate entry, which blows normalization out of the water, no?

Of the 300 or so providers, only about a dozen fall into the model where there a single corporate provider with multiple facilities and staff, but one contract. Another dozen or so have a single provider with multiple facilties and multiple contracts. The rest are all one provider, one facility, one contract.

I think the first relationship model you made is the right one in that it looks like how we think of our provider relationships, with the exception that the staff and contracts tables need to be switched.

Jackie

**orange** · 03-12-2012, 09:17 AM

My comments were simply to clarify and confirm WHAT your database is going to support. If a "rule" isn't correct, or not all the time, then adjust the rule, so that there is NO ambiguity. Then adjust the model to meet the rules.
I don't think you should proceed with a " sometimes but not always" - especially if this means you create/write some special programming to work around the "sometimes".
The versions of the model are just best guesses until you get the "rules" confirmed. Get some actual test data/transactions and see if the model works or not. I recommend you don't accept --sometimes this works. It will lead to other issues. Having said that, it doesn't mean you have everything perfect before you start. It just means there's some uncertainty in this or that, and it goes on the list to look at this in more detail.
Sometimes you have to ask others who are involved in the "business processes" to confirm your understanding of what they need/want/do. And sometimes you'll find they just don't know and it will be a learning experience for you and them--- all in the name of better communications.

**mitch455** · 03-13-2012, 08:43 AM

So moving forward with a model that will accommodate the majority of the providers and their "stuff." If I use the attached relationship model, what is the purpose of tblProviderLocation? How does that table get populated?

relationships.doc

**orange** · 03-13-2012, 09:27 AM

If you have taken the facts and info in your post #12, and tested your model with your knowledge of your business, and the model supports it, then go for it. You know your business better than anyone. My comments are just to help clarify any uncertainties.

It's this sort of ambiguity that I was pointing out that you should investigate and resolve.
"As far as contract and locations go, I guess they are linked, but it is not always a one-to-one relationship. Some contracts cover multiple locations, and many contracts are not for services provided at a particular location."

If you are comfortable with the facts, then adjust the model until you are convinced that the model supports your business facts.

As for "Having multiple Staff tables would cause a great deal of duplicate entry, which blows normalization out of the water, no?"
In my last model Post #8, it's the same single staff table - it's just representing different "roles" of the contact info.

My earlier term ProviderLocation was adjusted to ProviderFacility in that last model. If it doesn't fit in your understanding of the business, then remove it as you see fit.

I repeat, my comments are meant to help you clarify your situation and design.

Database designed the best way?

Thread Tools

Database designed the best way?

Similar Threads

Is this database designed okay?

Printing lables & reports on different printers than designed

New Access User needs help with Connection to different Database frm Current database

Java UI for Access Database : Select Checkbox items and add them to a Database table

Trouble extracting data from multpile tables (poorly designed relationships?)

Posting Permissions