Normalizing (and fixing) a large, messy DB (1 Viewer)

Mark_

Longboard on the internet
Local time
Today, 01:17
Joined
Sep 12, 2017
Messages
2,111
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).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:17
Joined
Feb 28, 2001
Messages
26,996
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:17
Joined
Feb 19, 2002
Messages
42,970
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.
 

JoeBruce

Registered User.
Local time
Today, 02:17
Joined
Jan 13, 2017
Messages
32
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:17
Joined
Feb 28, 2001
Messages
26,996
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.
 

Mark_

Longboard on the internet
Local time
Today, 01:17
Joined
Sep 12, 2017
Messages
2,111
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:

JoeBruce

Registered User.
Local time
Today, 02:17
Joined
Jan 13, 2017
Messages
32
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?
 

Attachments

  • Climb DB Relations 2.jpg
    Climb DB Relations 2.jpg
    87.2 KB · Views: 167

Mark_

Longboard on the internet
Local time
Today, 01:17
Joined
Sep 12, 2017
Messages
2,111
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.
 

JoeBruce

Registered User.
Local time
Today, 02:17
Joined
Jan 13, 2017
Messages
32
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.
 

Attachments

  • Climbing DB Relationships.jpg
    Climbing DB Relationships.jpg
    93.8 KB · Views: 156
  • Climbing DB Relationships Alt.jpg
    Climbing DB Relationships Alt.jpg
    90.9 KB · Views: 151

plog

Banishment Pending
Local time
Today, 03:17
Joined
May 11, 2011
Messages
11,611
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.
 

JoeBruce

Registered User.
Local time
Today, 02:17
Joined
Jan 13, 2017
Messages
32
Thanks for the quick reply, plog. I read it the other day and have been pondering your advice.

To answer your main follow up question, permits relate to the climbers who climbed and which routes they climbed. A permit can relate to a guide company, but only if one was present for the climb (in the context of data, that means a null value should be acceptable here). I might play around with the idea of a guide company relating to tblRouteRecords, especially in the context of your forms advice.

What you wrote about forms makes a lot of sense. I have done something similar, just not in the order you describe. I will experiment a bit and see what I can come up with.

I've realized the issue with forms based on queries; I generally use tables too, but when looking at the record set for the form I guess I turned it into a query. I had been referencing the other database I made, and saw that main form used a query. Now looking closer I do not think that form pulls data from more than one table, so the query there is superfluous. For this database, I've reverted back to the form based on a table; my subforms all seem to be select queries, though.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:17
Joined
Sep 12, 2006
Messages
15,613
Out of interest, do you know how much your firm spent on the database.
How many forms are there? How many queries?

Re-engineering a large database is an extensive and difficult project. Re-engineering a bad database is hard to justify. You may as well start over, but you have to convince someone that it's worth the effort and cost.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:17
Joined
Feb 19, 2002
Messages
42,970
I lost track of this thread but binding forms to queries is not a problem. All my forms are bound to queries - mostly so that I can use criteria to limit the record selections but sometimes to make it easy to show "lookup data" -- i.e. the ONE side of a relationship. To show the many-side of a relationship requires the use of a subform. So take an Order. On the order header, I want to show customer data. That is on the 1-side of the relationship so I can join tblOrders and tblCustomers and show customer data on the order form. However, orderDetails is the many-side of the relationship and so I need to use a subform to show data from tblOrderDetails.

In your case, you have multiple many-side relationships coming off the main table. EACH many-side table requires a separate subform. If you try to make a query that joins the three tables, it will be not-updatealble and it will show strangely "duplicated" data. The solution will be to correctly use separate subforms for each set of many-side data. Just because two tables are related to a parent table doesn't mean that they are related to each other.
tblCustomer-->tblOrders-->tblOrderDetails could be displayed in a single form as long as you are OK seeing the duplication of the first two tables for each item in the Details table
But
tblStudents-->tblClasses
tblStudentsr-->tblPets
cannot be shown in one query because student's pets have nothing to do with student's classes. So although you can create this query by joining tblClasses and tblPets each to tblStudents, the results will be a Cartesian Product (and not updateable) and the results will be strangely duplicated since it will show each of the pets attending Algebra and then each of the pets attending Geography, etc.
 
Last edited:

JoeBruce

Registered User.
Local time
Today, 02:17
Joined
Jan 13, 2017
Messages
32
@Gemma - my "firm" is a land management agency for the US federal government (insert necessary disclaimer about how everything I say and do here does not represent said agency). I'm sure the only money spent on creating the original database was the salary of the employee who did it. Although I've urged people to pay a pro and get a good product (build a normalized, user-friendly DB from scratch and import records into that from the old DB), that likely will not ever happen. I have made the efforts detailed in this thread out of a masochistic interest in MS Access, and because the current DB used for this is atrocious. I doubt it will become a finished product, but I may continue to plug away at the thing.

@Pat - thanks for an alternate perspective! There are always multiple ways to do things in this program. If and when I do start thinking about this, the advice of you, plog, Mark, and all will continue to help guide me.

In the mean time, I am actually brainstorming a different DB; but all of this is certainly helpful. In fact, I logged back in to check the advice given here to apply it to my design efforts. Cheers!
 

Users who are viewing this thread

Top Bottom