Question on Normalizing

[cdoyle]

Hi,

Just was asked to create a new db

Basically it's going to track provider/member information

Here is where I'm running into a issue.
I have a provider table, it has the name, rider#, and I need to list the 'clinic' the provider belongs too. I orignially thought since many providers can belong to the same clinic. I would created a clinic lookup table..

but then I started thinking, how in the world would I keep that updated? There are tons of clinics out there, and I have no way of knowing which ones need to be in the db.

So then I thought, should I just have clinic be a text field and the user will just type in the clinic name when they enter the new provider info? That would work, but it's also creates duplication in the db, and what happens if one person enters a new provider and spells the clinic name wrong? OR what happens if a clinic changes it's name all together?

I would have to run update queries to fix all that. Doesn't seem like the right way to go about this.

So how would you do it? Keep the database normalized, but also keep it easy for the users to update and maintain when creating records?

 

[pbaldy]

I'd probably have the clinic table, and use a combo box for the user to select from the existing clinics. You can then use the Not in List event of the combo to allow them to enter new clinics.

 

[KeithG]

You need a provider table, a clinic table and a ProviderClinic table. You can supply all provider info in the provider table, all clinic info in the clinic table. Then use the ProviderClinic table to associate a provider to a clinic. You will use the PK's from the Clinic and Providers table in the ProviderClinic table.

 

[cdoyle]

I'd probably have the clinic table, and use a combo box for the user to select from the existing clinics. You can then use the Not in List event of the combo to allow them to enter new clinics.

Ah OK, I've never used the not in list event before so never thought something like that.

Doing a search on google is brining up some info on it. Looks like it might work.

The only thing I worry about with it, say they go to type in a clinic name which is in the table, but they spell it wrong. That would prompt for adding a new clinic with the mis-spelled name right?

So then other users, might accidently select the mis-spelled one too?

 

[cdoyle]

You need a provider table, a clinic table and a ProviderClinic table. You can supply all provider info in the provider table, all clinic info in the clinic table. Then use the ProviderClinic table to associate a provider to a clinic. You will use the PK's from the Clinic and Providers table in the ProviderClinic table.

I need help visualizing how this would work.

The only clinic information there is, is the name of the clinic.
So the

clinic table would have

clinic_ID
Clinic_name

provider table

provider_ID
Provider_Name
rider_number

and the third table would have?
providerclinic_ID
provider_ID
Clinic_ID

how would this information be maintained by the users?
I still see the the problem of how to keep the clinic table updated.

 

[pbaldy]

That's true; I would add a yes/no message box saying "are you sure..." or something, and you could even add a password so that only certain people could add new clinics. You'd have to pass a spelling test to get the password.

 

[cdoyle]

That's true; I would add a yes/no message box saying "are you sure..." or something, and you could even add a password so that only certain people could add new clinics. You'd have to pass a spelling test to get the password.

lol wouldn't be a bad idea.

Thanks for your help, I think this sounds like a good, and simple solution that allows me to keep things normalized

 

[georgedwilkinson]

KeithG's solution is accurate and should be heeded. If you need assistance with it, just repost here.

pbaldy's suggestion of using a DDL will help prevent mis-spellings since the user can just select the correct clinic as he/she types.

Also, I have also found an algorithm called "Jaro-Winkler" to be of assistance when entering new data. I am working on a project now that will run a Jaro-Winkler comparison on items entered in the DDL, to help overcome mis-spelling errors.

 

[pbaldy]

KeithG's solution is only appropriate IF there's a many-to-many relationship between providers and clinics. While that may be the case, the OP's post does not indicate it, only that there can be many providers to one clinic. If that's the only possibility, then the OP's solution of a clinic field in the provider table would be appropriate.

 

[cdoyle]

It was my understanding that it would be many providers to 1 clinic.

But now that you mentioned it, I'm going to double check with the owners and verify that this is the case. I don't want to assume that when there could be a possiblity of a provider belonging to multiple.

thanks for bringing up that issue.

 

[KeithG]

Pbaldy is correct I made the assumption that there is a many to many relationship between clinics and providers. I have a feeling my assumption is correct though.

 

[cdoyle]

Pbaldy is correct I made the assumption that there is a many to many relationship between clinics and providers. I have a feeling my assumption is correct though.

I think you might be too, and I'm waiting a response back about it.

Glad I haven't had a chance to work on this much since I first started this post.

 

[georgedwilkinson]

I totally agree with KeithG that the M:M is the right thing to do, whether the client says it is possible now or not. Real world experience has proven that doctors/providers move between clinics, possibly over time and that users and/or domain experts don't look further into the future than a couple of weeks, unless we inform them of the possibilities.

What happens if you build it with an FK (clinic_id) in the providers table and one of the approved providers in that clinic works a double duty at 2 clinics, or leaves the company/chain and comes back at a later date? Also, Keith's solution maintains the cleanliness of the providers table.

A hybrid is to build both, with the associative table (ClinicProviders) having the assignment of clinics over time and the Providers table having the current or primary clinic assignment.

Safest to build it with the M:M. And much cheaper to do at this phase of the design than after you've already built it. You can still use the DDL strategies outlined above.

 

[cdoyle]

OK,
got word back and yes, there is the possibility of a provider being part of multiple clinics

Thank You for brining that to my attention, I just hadn't thought of that


OK, I created a new table called provider_clinic

I added the primary keys from my provider and clinc tables to this new table.
I then selected both fields and selected 'primary key'. so both ID fields have the primary key icon next to them. Is that right? That's what the access help said to do.

Also the junction table should have It's own ID field shouldn't it? I did a search and have seen it both ways.

 

[KeithG]

Yes that is correct both ID fields should be the PK. This is what they call a compostie key. I don't think it is nessary to add another ID field to the table.

 

[pbaldy]

The junction table having its own ID field is something of a personal preference. You'll probably have people jump in and argue vehemently for both sides. You've already established a compound key, so there's really no need for one, but some people would have one anyway. I probably wouldn't bother, but I wouldn't argue against it either.

 

[georgedwilkinson]

In the future, you may want to do something with your junction table. At that time, it might be appropriate to add a surrogate key (ID). Then you would want to make the current composite primary key into a Unique key and add the new artificial primary key.

An example could be something like keeping track of a providers events (timesheet, room schedule, etc.) by clinic.

 

[cdoyle]

Thanks,

I'm still a little unclear how this junction table works, or what it's suppose to do really.
I've never needed to do a M:M before.

What I had been planning on doing when I get to building my forms. Is have a main form, that used a subform for the provider. The user would push a button, where they would search for a rider number, if it was present they would select it, and it would display on the main form. If the number was not present, they could click a button to enter the provider information into the DB.

Now back to the db in general,

when the user is entering a new record for tracking,
The table that stores the provider action data, had a provider_ID wich had a relation to the provider_ID from the provider table.

But not really sure what I should be referencing with the introduction of the junction table.

also not sure if my form idea I had at first, still will work with all the multiple clinics a provider could have

 

[cdoyle]

I'm having a hard time trying to think of how to do this.

I want for each new record in the db, I want the user to be able to select a provider (the rider number is unique to each provider). So I'm going to have a search, or dropdown that allows them to see if that rider number is already been entered.

If not, they can add it (w/provider title), and then associate a clinic to this provider.

If the rider number is there, they'll be able to see if the clinic they want is associated to that provider already. If not, they'll add it.

Now back to the main form, for this new record they only need to see the provider/clinic for this record. The other clinics associated with this provider do not pertain for this new record.

I'm having a difficult time coming up with a way to do this.

 

[cdoyle]

I'm wondering..

Am I making this more difficult then it needs to be?

When I first started designing this, I thought that the provider and clinic needed to be tied together in somesort of relationship.

but really as I think about it more, the clinic is more tied to the 'issue' then anything else.

I'm wondering if I should have in my 'issue' table.
Main_Issue_ID pk
provider_ID
Clinic_ID
summary_of_issue
summary_of_findings



That way I can make a form, that lets the user select a provider and a clinic for each new record.