Need help with Relationships

[mpaulbattle]

I have been looking at youtube videos for days and researched this topic online for a long time. I am having an issue with creating relationships. I was told that I should break down my tables because it has too many fields. My tblPractices has close to 100 fields. So I have been working on breaking them down and trying to relate them to the tblPractices. Maybe I am breaking them down wrong. If someone can point me in the right direction it would be awesome. I attached a copy of the tables and my current relationships. If you noticed there are some left out, at the bottom. So my thinking is
tblMnemonic can only belong to one Practice tblPractice.
One practice can have many providers tblPractice to tblProviders
One provider can have many facilities or one Practice can have many facilities (multiple locations)

Breaking down the tblPractices i was thinking move the SCPorPCP and MHMDDept fields to their own separate tables.
We have 13 different modules that the practice can activate and out of those 13 some of them can activate certain providers. So even though the Module is activated for the practice, a practice with 5 providers maybe 2 are activated to use it. This is for reporting purposes. We track how many practices have a specific module activated and out of those practices how many providers are actively using it.

I'm still breaking down the tblPractices. Looks like i may have 30 different tables from that table.

You're also probably going to wonder why i have repeating fields such as Mnemonic. That is because I still need them there to match it on the tables. When i finish then I can delete the repeating fields.

[orange]

Forget tables and relationships for the moment.
Describe to us in plain simple English -just as you would tell someone in the line up at McDonalds -- WHAT is the "business"/"Activity" you are trying to automate or support with a database? 5 or 6 lines of real simple English.

Good luck.

[mpaulbattle]

Recently we moved our excel spreadsheet of all the practices / doctor offices to an Access DB. This was done just by using the Contacts template and importing the spreadsheets / workbooks (Practice Information, Practice Demographics, and Providers Information). The reason for doing this is because the spreadsheet was so big it took forever to find the information we needed sometimes. Also it was on a Sharepoint site and it didn't allow multiple users to make updates to it. The initial thought was to make it searchable by the practice name and to pull in all the practice information to one form. I was able to accomplish that, but we are unable to run a certain report and when i posted for help (on a different forum) regarding this report someone suggested i need to break up the tables and that may help. So I have been trying to break up the tables and set the relationships to see if that helps.

[orange]

Sort of, but not fully.
Access (database) and Excel(spreadsheet) are different animals that serve different purposes and are built upon very different object models.
You can not (read that as very,very rarely) transfer spreadsheet data directly to Access.
Access has certain concepts and principles (relational database) that, when followed, make development, use and maintenance quite manageable. If not followed/ignored, can lead to frustration and constant "work arounds".

With database you identify the "things"(entities) involved and how they relate to one another (business facts).

Here is a sample data model for a Doctors Practice from Barry Williams' site

Here are the associated Business facts that led to this generic model.

Here is a link to info re database planning and design

Good luck.

[mpaulbattle]

is it wise to have multiple foreign keys in one table? Lets say 10 FKs

[orange]

Without some context it is difficult to provide an answer and rationalize same.

Is it wise to have 5 bathrooms?

We need a business context.
Review the link(s) provided previously.

Having multiple FKs in a table is wise if that is what the business facts dictate.

[mpaulbattle]

I am trying this again. I have been trying to “normalize” my database as suggested by a member on this forum. Unfortunately, I am having trouble understanding relationships. I understand that one table has to have a like field in order for a relationship to exist. I am confused on which table should have the FK in it or do I need to create a junction table or have a table with composite keys.

What is the nature of my business? (I have been asked this and not sure how to answer it, but this is my attempt)

I work customer support for EMR (Electronic Medical Records ) software. For each customer we capture their business information: Name of the practice, the name and number of facilities they own, the addresses to the facilities, and providers who work for the practice.

Within the EMR they can have certain Features (Modules) activated to help them communicate with the patients, work more efficient or allow them to access their database remotely. We keep the date when the module was activated for the practice so we can run quarterly reports.

Currently we have one excel spreadsheet where the information is entered and right now that spreadsheet has over 100 columns. The idea is to put this information into Access so adding or updating is more manageable.

Question 1: Since the modules are practiced based (I’m thinking one-to-one) do I really need to remove them from tblPractices?

Question2: If so, why would it be beneficial to create separate tables for each module?

Question3: Would I need to create a junction table?

[orange]

Did you try working through any of the tutorials in the link I gave previously?

Try to expand the description of the "business" to show the things involved. If you take a typical day at the office approach, I'm sure you'll describe some of the processes that tie these "things" together.

[mpaulbattle]

I have looked at the links you provided. the DB model example is more for doctor offices. I tried to apply the concept to mine but IMO it's too different. We don't capture patient information. It would be more of a Contacts database. Advanced rolodex. Idk. Maybe I'll just leave it like it is.

[orange]

I think the tutorial(s) would help you with relationships, but you have to work through1 or 2.

For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials.
Class info system
Catering Business
Widgets

I'll help if you give it a try with your own data.

[mpaulbattle]

I'll try it again. I also looked at feeding time link

[orange]

Is a Customer necessarily an owner of a Facility? Or is the Customer the registered owner of the EMR software?
What exactly is a Practice vs a Facility?

Since your in Customer support for the supplier of the software, you have special needs related to modules that are active etc.
It seems you have admin/special routines that you can invoke to see what modules are being used, how often, by whom....

[mpaulbattle]

The Customer is the registered owner of the EMR software.

Practice - is basically the customer's name. Each Practice/Customer/Name of the Clinic can have one or multiple Facilities.
Facility - would be the physical Address. Each Facility can have one or multiple Providers.

We do reference the spreadsheet to which Practices are using which features. We see which practices have gone live on a monthly basis.

Just adding a screenshot of what i currently have set up. Not sure what to do with all the other module tables.

[orange]

There seems to be a lot of disjoint info in some tables.
If you are in software support, why are you looking at Practices, Providers etc? I'm just trying to isolate your interest/business from the business of the user.

You have software application contains modules and other info. Software is registered to Owner. Software maintenance/contractual info. Software support info provided to software an Customers.

The end customer uses the software to "manage" facilities, practices, providers and details related to these. His/Her "business".

[mpaulbattle]

So we are a re-saler of the software/application. The doctor office will purchase the EMR software from us and in turn we provide support to those offices. When the application has issues or they have how to questions we provide support to fix the issue or provide a resolution that works. When an office signs on we get their contact information so we can communicate with them through the installation process and training. When a new feature is available we will contact the practice to inform them of the new feature via email or telephone.