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: 2,112
Thanks: 20
Thanked 400 Times in 393 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,285
Thanks: 97
Thanked 2,032 Times in 1,979 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.


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: 29
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: 2,112
Thanks: 20
Thanked 400 Times in 393 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,285
Thanks: 97
Thanked 2,032 Times in 1,979 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.


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: 2,112
Thanks: 20
Thanked 400 Times in 393 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,770
Thanks: 93
Thanked 1,727 Times in 1,598 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: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 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 08-22-2019, 08:05 AM   #24
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 29
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 08-22-2019, 08:08 AM   #25
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,770
Thanks: 93
Thanked 1,727 Times in 1,598 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 08-22-2019, 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: 2,112
Thanks: 20
Thanked 400 Times in 393 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_; 08-22-2019 at 08:22 AM. Reason: Fix formatting
Mark_ is offline   Reply With Quote
Old 08-22-2019, 02:05 PM   #27
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 29
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, 16 views)
JoeBruce is offline   Reply With Quote
Old 08-22-2019, 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: 2,112
Thanks: 20
Thanked 400 Times in 393 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
Old 11-16-2019, 08:57 AM   #29
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 29
Thanks: 8
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

Hey all -

I am running into a big snag with my form design for this. I've traced the root of the problem to the many-to-many relationship(s), which create a "recordset not updateable" issue. Relationship screenshots are attached; the "Alt" is an attempt I made at circumventing this, which may work. But I feel like there should be a way to make the original work. The issue is still with recording guides for guided climbs.

My main form has two subforms already, one for logging climbers and another for routes. I ran into a few different issues when trying to incorporate a field to indicate which company guided, and/or which climber acted as a guide for the climb. Remember that not all climbs are guided, guides can climb recreationally (not guiding), and guides can work for more than one company (only on separate climbs).

The "Alt" version allows me to tie the permit directly to a company, and tblClimbers was modified to indicate if a climber is/can be a guide. I would like to find a solution via the other version, though; plus that is what the previous input and responses are all based on in this thread.

Any input on creating forms around this kind of relationship structure? Basically, it seems like Access can't incorporate tblGuideFor into a form for tblPermits, because there is no direct link between the two.
Attached Images
File Type: jpg Climbing DB Relationships.jpg (93.8 KB, 9 views)
File Type: jpg Climbing DB Relationships Alt.jpg (90.9 KB, 9 views)
JoeBruce is offline   Reply With Quote
Old 11-16-2019, 10:48 AM   #30
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,433
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Normalizing (and fixing) a large, messy DB

Both relationships look good. This is really on you to decide which is correct because you know your data better than us. It comes down to what permits relate to. Do they relate to a climb company? Not to muddy the records more, but could climb companies be directly related to tblRouteRecords instead?

I can help you with forms. My rule is that forms that interact with data (add/update/delete) should be based on tables. Your issue isn't form related, its query related. The query you made that you based your form on isn't updateable because of a JOIN most likely. I say avoid those issues by basing your forms on tables--you will never have that issue.

Second, when you have a string of 1-many tables like you do (RouteRecords->Permits->Climbers) you use a series of main form with sub forms. The first main form would be based on tblRouteRecords with a subform based on tblPermits. The main form would show just one record from tblRouteRecords and the subform would show all related records from tblPermits. On the subform for tblPermits, next to each record be a button, when clicked it opens a new main form based on tblPermits and showing just the data for that one selected record. At the botom of the main form for tblPermits will be a subform for tblClimberRecords showing all related records for that permit. Next to each record in the subform will be a button that opens a main form for that selected climberRecord record which will have a subform--etc. etc. Just work down your rleationship like that with forms.

plog 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 07:50 PM.


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