Same table or new table (1 Viewer)

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
I was wondering...

In my database i have a table of providers, tblproviders, which captures their name, credentials, join date, end date, and any products they have activated (such as emobile, emessenger, eHx, etc.). Each provider is part of an incentive based program and we need to capture which stage they are currently at in the program. My question is should I add the incentive fields to tblProviders or create a new table?

Each year the stages will be updated based on program so I would have to add new fields as necessary.
 

isladogs

MVP / VIP
Local time
Today, 09:22
Joined
Jan 14, 2017
Messages
18,186
It sounds like there will over time be several records for the same provider as they move from one stage to another

Therefore you need a separate table with a one to many relationship to your existing table
 

plog

Banishment Pending
Local time
Today, 04:22
Joined
May 11, 2011
Messages
11,611
...and any products they have activated

Sounds like you need a new table to hold the products they have activated as well. These currently seperate fields or all jammed together as text in one?
 

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
They are separate fields. Yes or No responses. They run reports to see how many providers are actively using a certain product.
 

plog

Banishment Pending
Local time
Today, 04:22
Joined
May 11, 2011
Messages
11,611
Need a new table for that then:

tblProducts
ID_Provider, ProductType
17, emobile
17, emessenger
18, emessenger
19, emobile
19, eHx

ID_Provider is a foreign key back to tblProvider.
 

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
I didn't capture it in the screen shot but the ID field is MUID_PK | AutoNumber.

Would I break this table up into different years or just add on to it?

It seems easier if I just add on to it. Then I could just query off one table.

Or is that not a good thing? Just asking for future reference.
 

Attachments

  • tblMU.jpg
    tblMU.jpg
    86.2 KB · Views: 204

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
Need a new table for that then:

tblProducts
ID_Provider, ProductType
17, emobile
17, emessenger
18, emessenger
19, emobile
19, eHx

ID_Provider is a foreign key back to tblProvider.

I tried it like that, but I kept getting confused on how to link the two tables. I'm still trying to figure out how to do relationships.
 

plog

Banishment Pending
Local time
Today, 04:22
Joined
May 11, 2011
Messages
11,611
Looking at the screenshot you posted, you really need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). that's the process of correctly structuring your tables.

The biggest sin I see is that you are contantly storing values in field names. Never should a field name contain a year, or quarter or basically any type of number (e.g. Stage). Instead, like the table I laid out prior, those values should be stored in a field (e.g. StageNumber, FiscalYear, FiscalQuarter).

Again, read up on normalization. Then, work through a few query tutorials (https://www.w3schools.com/sql/) so you can understand how to link tables together to retrieve data.
 

Mark_

Longboard on the internet
Local time
Today, 02:22
Joined
Sep 12, 2017
Messages
2,111
Each year the stages will be updated based on program so I would have to add new fields as necessary.

Regarding normalization, ANY TIME you run into an issue like this you should immediately plan it as a separate table. Within the table you would hold the date it is effective for as well as what you would be storing.

For the most part, any time you run into "Should I add extra fields OR should I have a couple fields in a new table?" The answer is almost always "Add the new table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:22
Joined
Feb 19, 2002
Messages
42,970
To put it simply,
If you have more than one of something, you have many. Many ALWAYS requires a child table.
 

Cronk

Registered User.
Local time
Today, 20:22
Joined
Jul 4, 2013
Messages
2,770
And if you have need to have fields in the one table such as 2015xxx, 2016xxx, then that table is definitely not normalized. You have an entity which is occurring annually, related to the parent record and should be in a child table.
 

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
i have read up on normalization and attempted to put what I think I learned into action. Can someone look at my screenshot and let me know if I am close?

Thank you in advance
 

Attachments

  • MPLRelationships.jpg
    MPLRelationships.jpg
    91.4 KB · Views: 149

Minty

AWF VIP
Local time
Today, 09:22
Joined
Jul 26, 2013
Messages
10,353
Not quite.

All those FK fields that are stored in your main table shouldn't be there.
You should move the PK field of the main table into your tables where those FK tables as the FK link.

So tblContact Info should have

ContactID_PK
PracticeID_FK
...
...

This then allows you to link multiple contacts to a specific Practice.
Does that make sense?

You may need to have a junction table if (for instance) your providers could work with more than one practice. Then you would have a simple table something like

tblParacticeProviders
PracProvID_PK
PracticeID_FK
ProviderID_FK
 

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
Minty,

Thanks for the reply. I do understand the recommendation regarding the Contact and practice tables. One practice can have many contacts. The only thing I am confused about is moving the PracticeID_PK to the other tables.

The ACO, Dept, PracticeStatus, etc. are Yes/No or a certain Status.

tblACO (yes,No)
tblDept (CI, CI APP, CI APCP, etc.) Practice belongs to one of these

So i was thinking these could belong to multiple practices because a practice can't have multiple values in these tables. Does that make sense?

I will make the changes you suggested and maybe it will clear up the picture for me.

thanks again.
 

Minty

AWF VIP
Local time
Today, 09:22
Joined
Jul 26, 2013
Messages
10,353
When I said all of them - that may have been a exaggeration - i didn't inspect every tables purpose.
Generally if you are storing data related to the practice then the practice ID should be in the child table as a FK, not the other way around.
 

Mark_

Longboard on the internet
Local time
Today, 02:22
Joined
Sep 12, 2017
Messages
2,111
You have three tables that may be redundant in actual use...
TblEmploymentType
TblProviderStatus
TblPracticeStatus

These look like types of lookups.
For myself, to avoid a LOT of headaches I always store these types of values in their own "TblLookups". That table has
LookupsID - Autonumber - PK
LookupType - Text - Hold a reminder as to what field(s) will use this lookup
LookupValue - Text - Holds the actual value returned from the lookup.
(OPTIONAL) LookupSort - Number - Numeric "Sort order" for when I want to arrange which values appear first.

This lets me have ONE table with several queries support all of my static lookups. This can avoid a lot of development issues when you need specific values for coding reasons that should not change. When I see "ProviderStatus" and "PracticeStatus" those almost scream to me "We don't change" and "The developer can do fun things with us".


While this does touch on normalization, it is also a development decision that can be very useful. I also have entries in my Lookups table to tell me what lookups I am using (the LookupType Combobox is driven off of a query on TblLookups).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:22
Joined
Feb 28, 2001
Messages
26,996
mpaulbattle, let me try to explain the type of decisions you have to make when you are going to normalize tables.

The first thing you need to do is to analyze what kind of entities you deal with. I'm going to take the issue of doctors and patients in a medical office as an example of the "right" line of questioning.

If you deal with people (doctors, patients, nurses, etc) that can be one type of entity or two (either all people, some of whom are doctors and some of whom are paients, or two entities to keep data separate). Deciding on one or two types of entity will depend on the business rules regarding the way the application must treat the two.

If patients have visits with doctors, the record of the individual visit is another type of entity. The idea that a doctor has a "practice" means that you have a third type of entity. Some of the actions a doctor could perform are yet another entity, perhaps a "treatment." In brief, different entities each get their own table based on commonality of business model rules.

It is possible for these entities to be directly and indirectly related to each other. It is possible for multiple relationships between entities. When doing the data analysis, what you look for is the nature of the relationship.

For instance, if a patient visits a doctor, you have the "visit" which requires both a doctor and a patient. So that is a double-barreled dependency. You can have a patient and you can have a doctor whether or not they see each other, so the patient and doctor are independent entities. But the visit requires BOTH patient and doctor, so it is a dependent entity. The treatment occurs in the context of a visit, so treatments are dependent on visits. The doctor and patient can see each other many times (sort of the definition of a practice, I believe), so you can have a many-visits-per-patient and ditto for the doctor. Thus, you have a pair of many-to-one relationships. Which also means that each visit is a separate entity from other visits. So you need something about a visit to tell two visits (same doctor, same patient) apart, perhaps such as a date or a sequence number.

What good is this kind of discussion? Well, the entities that are dependent on something else higher in the hierarchy of your structure are the ones that must have foreign keys (FK). The things on which they depend are the ones that must have prime keys (PK). So this kind of question helps you decide where the keys will go and how they will be used. It IS possible to have both a PK and some FKs associated with the same entity.

So the next kind of question that you must decide is, what data goes where? Sometimes it is easy; sometimes, not so much. For the doctor-patient-visit-treatment structure, the doctor entity is described by a doctor table. The information about the doctor might include phones, addresses, certifications, etc. Things where the doctor only has one (such as "home address") might be in a Doctors table. Specialties or certificates, of which the doctor might have many, would go in a child table with a one-to-many relationship with the doctor.

The idea of a Practice depends on how you define it. If you have multiple doctors sharing a practice, a question would be whether they share patients as well, or whether the doctors only share offices and business staff but are exclusive to a given patient.

These are the questions you would ask when normalizing so that you can find the right number and content of tables. I know it can be completely confusing and frustrating - but if you have a good understanding of your business model, you should be able to get a good data layout quickly.
 

mpaulbattle

Registered User.
Local time
Today, 04:22
Joined
Nov 30, 2017
Messages
56
You have three tables that may be redundant in actual use...
TblEmploymentType
TblProviderStatus
TblPracticeStatus

These look like types of lookups.
For myself, to avoid a LOT of headaches I always store these types of values in their own "TblLookups". That table has
LookupsID - Autonumber - PK
LookupType - Text - Hold a reminder as to what field(s) will use this lookup
LookupValue - Text - Holds the actual value returned from the lookup.
(OPTIONAL) LookupSort - Number - Numeric "Sort order" for when I want to arrange which values appear first.

This lets me have ONE table with several queries support all of my static lookups. This can avoid a lot of development issues when you need specific values for coding reasons that should not change. When I see "ProviderStatus" and "PracticeStatus" those almost scream to me "We don't change" and "The developer can do fun things with us".


While this does touch on normalization, it is also a development decision that can be very useful. I also have entries in my Lookups table to tell me what lookups I am using (the LookupType Combobox is driven off of a query on TblLookups).

I will have to look into lookups. I saw this when I used the Analyze Table but I didn't understand it, and plus it separated fields i believe should have been put in the same table, i.e. emobile, emobileactivationdate, emobilecode. All three were in different tables so it didn't seem right to me. :banghead: I will research this too. Thanks for reply.
 

Mark_

Longboard on the internet
Local time
Today, 02:22
Joined
Sep 12, 2017
Messages
2,111
Before you go into too detailed of a search, there is a fundamental question you need to ask for each field you are going to use a lookup on; "Should its value change if the table used for the lookup changes?"

In most cases, yes you will want changes to replicate. In some cases you will want to retain data that is now "Obsolete", so you will either need a flag in the table for lookups saying "This record is no longer used" OR you will need to store the actual value in the record rather than a pointer to the value in the lookup table.

For some specific business rules you do actually save the value. This is normally because there is a regulatory or auditing reason to save EXACTLY what was there rather than a pointer to something that could be changed.

Just keep in mind that you have "By reference" and "By value" lookups. By reference is the normal "Keep a copy of the PK for the looked up value" while by value is "Copy the value from the table I'm looking it up from". This will help avoid some confusion if you start reading about one or the other.
 

Users who are viewing this thread

Top Bottom