Question about Relationships 1-to-Many or 1-to-1 (1 Viewer)

isladogs

MVP / VIP
Local time
Today, 13:48
Joined
Jan 14, 2017
Messages
18,186
One other bit of praise for you D_C. :)
After I pointed out the cross-posting etiquette just after you joined AWF, you were scrupulous about informing users on both sites about answers given at the other site.
That's how to use cross-posting both effectively and in a way that causes no offence to anyone.

Having said that I had to laugh when you posted a 'UA Rulez' emoji here!
Try mentioning 'Access World Forums' in a post at Utter Access.
As I know to my cost, it will immediately be censored and rewritten as 'another forum'.
And don't even think of questioning that action if you want to remain a member at UA! :eek:
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 10:48
Joined
Dec 24, 2018
Messages
150
The phrase lookup tables is the standard terminology. As i said before, they are a very useful tool for guiding users to choose from the available items using combo boxes in forms. However they should NEVER be used at table level. - see the evils of lookup fields in tables
All data should in any case only be edited / entered using forms

That post about the evils of lookup fields was haunting me for a while, but I decided to search here about alternatives. I found simple explanation from jzwp22 that opened my eyes! If any n00b fellow is interested please check here: https://access-programmers.co.uk/forums/showthread.php?t=207989

Now I know how to handle it and knowing is half of the way! :)

I’ve made various suggested changes to your database but have kept all except two of the lookup tables.
However, I have removed all table level lookups (combos in tables) – all data should only be edited using forms
I’ve also scrapped your attachment field – I originally assumed it was a MVF – but attachments are equally bad!

I read it carefully and I think I agreed on almost everything! Thanks you very much for the help and support!

Full details of changes I've made are in attached Word doc
I attached a response to it, also I included the answer to all the questions you raised.:)
 

Attachments

  • Suggested Changes to SOC Mgr Example database.docx
    23.5 KB · Views: 157
Last edited:

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 10:48
Joined
Dec 24, 2018
Messages
150
One other bit of praise for you D_C. :)
After I pointed out the cross-posting etiquette just after you joined AWF, you were scrupulous about informing users on both sites about answers given at the other site.
That's how to use cross-posting both effectively and in a way that causes no offence to anyone.

Thanks! I did feel that was the least I could do after reading the etiquette post. :eek:

Having said that I had to laugh when you posted a 'UA Rulez' emoji here!
Try mentioning 'Access World Forums' in a post at Utter Access.
As I know to my cost, it will immediately be censored and rewritten as 'another forum'.
And don't even think of questioning that action if you want to remain a member at UA! :eek:

:D

Here is something else for you to laugh: one of my bosses is from Alabama and he has a very strong accent, every single time he says "You all" it sounds "YOL" but in my mind, for some mysterious reason, I always understood it as "UALL".
When I saw that emoji I did not notice that the "UA" stood for their website name... I thought it was short for "YOU ALL RULEZ" because of my boss way of saying "you all"!
The joke was on me TWICE!
 
Last edited:

June7

AWF VIP
Local time
Today, 05:48
Joined
Mar 9, 2014
Messages
5,425
My 2 cents …

My first db was for a construction materials testing laboratory (I inherited it partially built). Samples of various material types are tested in a number of diverse procedures selected from about 200. 1-to-1 relationship between the sample login record and a table for each test procedure was determined the best approach so that raw test data could be saved, not just calculated result (as was done in the old dBase system). Not every test is run for every sample and sample ID can be in a test table only once. Relationships are not established in Relationships builder (too many tables) - data integrity is managed by code and form design as well as modified ribbon, disabled shortcut menu, etc.

Primary purpose of db is to manage data for the generation of individual sample report. Design focused on most easily accomplishing data input and output towards that end and violates several normalization principles. There is very little statistical analysis of the data. Do have to resort to a few UNION queries in order to produce some graphs for the report. All has been working quite well for over 10 years now. It will never be a very large db. The previous 20 years processed only about 65,000 samples. There are still less than 5000 samples a year.

It is a balancing act between normalization and data entry/output. Normalize until hurts, denormalize until it works.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:48
Joined
Feb 28, 2001
Messages
27,003
What if the Safety team decides they will start tracking more information about the Areas, Departments and Companies because they feel it is becoming more dangerous and receiving a lot of cards then I would have to change the static tables to remove them from there and create their own tables and re-work on the code? Then these tables will be normalized because it will have more characteristics for those entities.

I want to address this question carefully.

First, as I suggested, you would not include a static lookup table with other such multi-column lookups if there was a really large size difference. Like, five to ten translations for each of three potential lookups but over 100 possible translations for a fourth lookup. That would be grounds for not including the fourth lookup.

Second, if the table isn't really that static, but rather is subject to change on short notice, you might not wish to include it in the multi-column lookup. That would be because it might become a pain in the toches to keep the other columns in their proper order while diddling with the column being changed.

The ONLY time this multi-column lookup table makes sense is if the tables are REALLY static. If you anticipate updates such as a very uneven increase in potential records for this putative lookup table, perhaps you would not want to go this way anyway. ANYTHING that would cause you to have to "churn" the tables would qualify as a reason to not include that particular translation.

If you have to assign specific codes to specific translations (for some reason known only to unnamed powers-that-be), doing so means the key is no longer synthetic. In and of itself, this would not be a show stopper, but it would be an indicator that diddling with the other columns of the table might become a problem if they still retain the autonumber key.

June7's comment is worth a bit of contemplation, too. There IS such a thing as overdoing the normalization. Sometimes in order to display something in a particular way, you might have to denormalize your data. Particularly if lists are involved and there is a standard format that doesn't "fit" a normalized table very well, you might have to merge some child tables to concatenate the child records into a single record.

You WANT to normalize because Access gains greater manipulative power (well, SQL gains the power) when you normalize. But it IS a balancing act, because sometimes as the tables get more and more normalized, the idea of "compact reporting or displays" goes out the window.
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 10:48
Joined
Dec 24, 2018
Messages
150
My 2 cents …

...
It is a balancing act between normalization and data entry/output. Normalize until hurts, denormalize until it works.

June7, those 2 cents plus that final sentence worth a lot to me thanks!
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 10:48
Joined
Dec 24, 2018
Messages
150
You WANT to normalize because Access gains greater manipulative power (well, SQL gains the power) when you normalize. But it IS a balancing act, because sometimes as the tables get more and more normalized, the idea of "compact reporting or displays" goes out the window.

I sense I am starting to grasp the Normalization Theory vs Real World problems...

Thanks for the ponderation about the multi-column lookups tablem I will have that in mind.

In fact, I am very happy with this forum because I have learnt a lot with you guys!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:48
Joined
Feb 19, 2002
Messages
42,981
Access can be told to "damn it all" MAKE that a 1:many field. You can override its suggestion.
The designer does NOT specify the cardinality of the relationship. Access determines it based on how the tables are joined. PK-PK = 1-1. PK-data = 1-m.

Please forgive me if I've repeated any other suggestions.

1. Your naming is inconsistant. For example, you have ObserverFK --> Observer s ID. Either use the "s" or don't. UserNameFK --> UserID - this one may be a "lookup". If so, get rid of it. There are lots of posts explaining why. Same for AuditTrail
2. tbl1Cards --> tbl1Feedback is 1-1 and almost certainly should not be. Making it 1-1 means that you can have only a single feedback record for the card. Access is assuming the 1-1 probably because FardFK is defined as unique. If this is 1-1, then get rid of FeedbackID because it is redundant and make CardFK the PK of the table.
3. The numbers in the table names don't have an obvious meaning.
4. Supervisors are probably Users and so should not be in a separate table. They should be in the Users table with a SupervisorFlag field.
 

Users who are viewing this thread

Top Bottom