Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-26-2018, 05:30 AM   #1
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Same table or new table

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.

mpaulbattle is offline   Reply With Quote
Old 02-26-2018, 05:33 AM   #2
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,291
Thanks: 77
Thanked 1,288 Times in 1,204 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Same table or new table

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
mpaulbattle (02-26-2018)
Old 02-26-2018, 05:49 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,637
Thanks: 10
Thanked 2,071 Times in 2,026 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Same table or new table

Quote:
...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?

plog is offline   Reply With Quote
Old 02-26-2018, 06:03 AM   #4
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

They are separate fields. Yes or No responses. They run reports to see how many providers are actively using a certain product.
mpaulbattle is offline   Reply With Quote
Old 02-26-2018, 06:11 AM   #5
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,637
Thanks: 10
Thanked 2,071 Times in 2,026 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Same table or new table

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.
plog is offline   Reply With Quote
Old 02-26-2018, 06:16 AM   #6
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

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.
Attached Images
File Type: jpg tblMU.jpg (86.2 KB, 35 views)
mpaulbattle is offline   Reply With Quote
Old 02-26-2018, 06:19 AM   #7
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

Quote:
Originally Posted by plog View Post
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.

mpaulbattle is offline   Reply With Quote
Old 02-26-2018, 06:29 AM   #8
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,637
Thanks: 10
Thanked 2,071 Times in 2,026 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Same table or new table

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.
plog is offline   Reply With Quote
Old 02-26-2018, 06:57 AM   #9
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

will look into it again
mpaulbattle is offline   Reply With Quote
Old 02-26-2018, 04:46 PM   #10
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,176
Thanks: 13
Thanked 221 Times in 219 Posts
Mark_ will become famous soon enough
Re: Same table or new table

Quote:
Originally Posted by mpaulbattle View Post
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.
Mark_ is offline   Reply With Quote
Old 02-27-2018, 05:29 PM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,109
Thanks: 13
Thanked 1,346 Times in 1,282 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Same table or new table

To put it simply,
If you have more than one of something, you have many. Many ALWAYS requires a child table.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 03-27-2018, 11:51 PM   #12
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,745
Thanks: 2
Thanked 369 Times in 365 Posts
Cronk will become famous soon enough
Re: Same table or new table

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.
Cronk is offline   Reply With Quote
Old 05-24-2018, 03:53 AM   #13
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

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
Attached Images
File Type: jpg MPLRelationships.jpg (91.4 KB, 17 views)
mpaulbattle is offline   Reply With Quote
Old 05-24-2018, 05:01 AM   #14
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,289
Thanks: 124
Thanked 1,433 Times in 1,405 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Same table or new table

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 05-24-2018, 05:34 AM   #15
mpaulbattle
Newly Registered User
 
Join Date: Nov 2017
Posts: 29
Thanks: 2
Thanked 0 Times in 0 Posts
mpaulbattle is on a distinguished road
Re: Same table or new table

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.

mpaulbattle is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
cross-table query not shown correctly in table from make-table query killerflappy Queries 5 10-09-2017 10:53 PM
Populate table name in sep created table, each time a table is added snoopydoopy Queries 1 10-23-2014 04:43 AM
form for table A to search fields from table B and populate into table A johnseito General 16 08-06-2013 06:25 PM
Relationships between table - 3 fields in 1 table related to another same table laiching Forms 2 01-16-2012 06:20 PM
Naming a table created with a make table query from a field in the table? AllanN General 4 07-03-2002 04:52 PM




All times are GMT -8. The time now is 01:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World