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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2019, 07:36 AM   #16
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Normalizing (and fixing) a large, messy DB

One question,
Why are guides a child record of climbers? Wouldn't one (or more) guides be on a permit? As is it looks like each climber has multiple personal guides.

If a climber can actually be a guide, you may want to put in a linking table between climbers who are guides to what ever you need to link them to.

Mark_ is offline   Reply With Quote
Old 08-13-2019, 07:38 AM   #17
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Normalizing (and fixing) a large, messy DB

You may get some insight from this post and approach.
Good luck.
__________________

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.
jdraw is offline   Reply With Quote
Old 08-13-2019, 11:55 AM   #18
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 27
Thanks: 8
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

@Mark - Not all climbs that happen here are guided. Most climbs do not have a guide, some do. And several of the guides also climb recreationally (i.e. they aren't working/guiding).

Hopefully that helps clarify the situation. The park needs records of the guides and who they work for. And they want to track which climbs are led by a commercial company (guide). Any guide would be a climber. Maybe the error here is a one-to-many relationship; in reality it would be a one-to-one. (I think; a guide = a climber, but a climber <> a guide; so maybe join the tables on the two PKs?) That side of the relationship layout may still need some work. Well, the whole thing may still need work!

Any thoughts or advice on my recent question? Is there a better way for tblPermits to store multiple climbers per record?

@jdraw - I understand your advice, but the context of my scenario is a bit different. I will be coordinating with the folks who use the climbing DB, but we're all front-line employees at a unit of a large public agency. I'm just the guy who knows Access and we're all really annoyed with the current DB we have here. I doubt this new one will actually get used, this is more of a personal challenge to see if I can develop something better.

JoeBruce is offline   Reply With Quote
Old 08-13-2019, 12:56 PM   #19
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Normalizing (and fixing) a large, messy DB

JoeBruce,

The linking record IS the best way to attach climbers to a climb.

Do your guides ever work for more than one company?
Mark_ is offline   Reply With Quote
Old 08-13-2019, 01:15 PM   #20
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,037
Thanks: 78
Thanked 1,995 Times in 1,943 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Normalizing (and fixing) a large, messy DB

JoeBruce,
What Mark is leading you through are the questions to determine the rules of your business. They are key elements in determining the relationships for tables in your database. This is part of analysis and the "stump the model" exercise is to verify those rules and the proposed structure/blueprint for your database.
Good luck with your project.
__________________

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.
jdraw is offline   Reply With Quote
Old 08-13-2019, 03:22 PM   #21
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Normalizing (and fixing) a large, messy DB

JoeBruce,

Looking through you data, you may be able to drop the "TblGuides" entirely. You have a field "IsGuide" in tblClimbers. If you replace this with GuidCompanyID, this will mean that the climber is also an employee of that company.

If the climber can be a guide for more than one company, you'd want to use the same type of table as "ClimberRecords" to link a climber to one or more companies.

This also means that in your "ClimberRecords" you can have a an "IsGuide" field there to indicate that, for that given climb, they are a guide.

This means that when you look at permits you will see all of the climbers on a permit AND see who is a guide for that given permit. Unless someone can change between being a climber and guide DURING a climb, this should model what you are looking for when you attach climbers to permits and want to indicate who's guiding them (if anyone).
Mark_ is offline   Reply With Quote
Old 08-14-2019, 05:55 AM   #22
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,093
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Normalizing (and fixing) a large, messy DB

Quote:
in reality it would be a one-to-one. (I think; a guide = a climber, but a climber <> a guide; so maybe join the tables on the two PKs?)
One-to-one relationships are actually quite rare and this doesn't sound like one of the cases where such a relationship is justified. While this fine point is not always fully appreciated, it is best that when your real-world case includes a one-to-NONE case, you make the table have a one-to-many relationship because one-to-many allows you to include the one-to-one AND one-to-none cases as a subset of "many." It allows you to handle JOIN cases where there IS no matching record. In the one/one relationship for an unmatched record, neither record would be reported, whereas for the one/many case, an unmatched record can still show up in the JOIN, merely having nulls for those fields that would have come from the putative matching record.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-15-2019, 06:31 PM   #23
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Normalizing (and fixing) a large, messy DB

I disagree with Plog's #3. Although I have a method that allows me to consolidate multiple simple lookup values into a single table, I believe that they should be tables. In addition to the ID and description, my tables always have a third value which is InActiveFlg. That allows me to keep items in the list for reference but to not allow their use in new records. Since I put all my lookup tables in one table and manage them all with a single form, I use autonumbers. If I were to create separate tables, I would use the string values just to eliminate the joins just to lookup a description.

I am confused about the presence of ClimberID_FK in the climb table. That implies that the climb had only a single climber in the group. I think you actually need a junction table between climbRecords and Climber. That allows each climb to include multiple climbers.

The relationship between Climbers and Guides is less of a mystery since I assume that a Climber might at some point act as a guide.

The relationship between Climb records and GuideCompanies should probably be through the actual guide, again using a junction table since a climb probably can have multiple guides. So the junction table is ClimbRecordID -- GuideID and then if you also need the guide's company, you would get it by joining tblGuides to tblGuideCompanies. As you have the relationship built, all guides are connected to each climb and I'm pretty sure that is incorrect.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old Yesterday, 08:05 AM   #24
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 27
Thanks: 8
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

Thanks for the continued input, all.

@Mark - Your question (can a guide work for more than one company) was a provoking one, and I am determining if that is something the DB needs to plan for. I am (re)reading your last post and (slowly) wrapping my head around it, figuring if that is the way to go or not.

@Doc - thanks for the explanation. The idea didn't feel right after I considered it, anyway.

@Pat - yes, I have also done "lookup" data in separate tables, and it worked fine for me. I did not mind trying it the way plog (and Mark) suggested, as that seems to work well too. As for the rest of your post, the ClimberID_FK allows me to attach multiple climbers to a permit; it is the linking table between climbers and permits (climbs). And yes, a permit can have multiple guides, so I likely have to rework this. I think the answer is somewhere in yours and Mark's posts...

Looking at the current DB used to track permits, I realized that a permit can also have multiple routes (i.e. the climbers did multiple climbs, logged on one permit). So I'm about to tackle that with a linking table between tblPermits and tblRoutes. I'm also getting input from my coworker who represents a front-end user of the DB, going through some of the exercises jdraw encourages.
JoeBruce is offline   Reply With Quote
Old Yesterday, 08:08 AM   #25
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,093
Thanks: 81
Thanked 1,601 Times in 1,485 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Normalizing (and fixing) a large, messy DB

Quote:
I'm also getting input from my coworker who represents a front-end user of the DB, going through some of the exercises jdraw encourages.
There is NO substitute (none, nada, zip, zilch) for getting feedback from an actual user. It is the best path to take, bar none! You will learn so much from the end users, things that the boss never saw or never considered.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old Yesterday, 08:20 AM   #26
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Normalizing (and fixing) a large, messy DB

IF a climber can be a guide, and IF they can work for different companies, I'd suggest adding a table that link guides to companies.

Code:
T_GuideFor
GuideForID	AutoInc - Primary Key
GuideID		Number - FK to link to a Guide
CompanyID	Number - FK to link to Company
DtStart		Date  - When they started working for the company
DtEnd		Date - When they STOPPED working for the company
GuideNote	String - Any special notes regarding this guide's relationship, will probably be blank
This will allow you to link climber to the companies they work for as guide. It also includes when a guide stops working for a company, so you may have more than one matching pair between a climber and a company if a climber stops working for a season or two.

The notes will probably wind up with anything special that a guide is certified in, such as their skill rating, any special equipment/medical training, or additional languages.

Last edited by Mark_; Yesterday at 08:22 AM. Reason: Fix formatting
Mark_ is offline   Reply With Quote
Old Yesterday, 02:05 PM   #27
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 27
Thanks: 8
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

Alright, screenshot attached for latest relationship layout. Guides can work for more than one company, so the linking table is added. TblClimbers got adjusted too with how it links to tblGuides. I also added a linking table between tblPermits and tblRoutes, so we can choose multiple routes per permit.

tblPermits has fewer fields, but that is a good thing. I was thinking of Mark's earlier advice regarding how to indicate that a climb was led by a guide. The "ClimbPurpose" is a lookup field which includes several options, one of which is commercial guide. That should be adequate for later determining which climbs were guided, no? I also eliminated the "number of climbers" field, because that should just be calculable data I get from my other tables.

Any additional thoughts?
Attached Images
File Type: jpg Climb DB Relations 2.jpg (87.2 KB, 3 views)
JoeBruce is offline   Reply With Quote
Old Yesterday, 10:01 PM   #28
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,945
Thanks: 19
Thanked 370 Times in 363 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Normalizing (and fixing) a large, messy DB

From your layout, it looks like TblGuides is not actually needed.

A person (climber) can be a guide.
The way you can tell they are a guide would be by having there be a link between them an a company (tblGuideFor) and this would have a many to many relationship to BOTH tblClimbers and TblGuideCompanies.

As is, it looks like you can have many Climbers attached to ONE guide record, but this doesn't make much sense when you want to show for a given permit who the guide is.

TblClimberRecords should show who is a guide for that climb, assuming a guide is for the entire climb and nor for a child route. Likewise your layout work fine if all climbers on a permit go to all routes.

Mark_ 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
Scattered fields make a messy table hilian Tables 16 07-10-2015 11:22 AM
A very messy Count. pr2-eugin Queries 2 01-03-2013 06:22 AM
Import from Excel - Messy maw230 General 10 08-05-2010 10:29 AM
[SOLVED] VBA routine that would organise my messy table kalevi12 Modules & VBA 3 04-03-2006 04:23 AM
messy screen during runtime striker Modules & VBA 1 05-09-2002 01:00 AM




All times are GMT -8. The time now is 04:19 AM.


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

Featured Forum post


Sponsored Links


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