How do I normalize my tables correctly? (1 Viewer)

5hadow

Member
Local time
Today, 05:45
Joined
Apr 26, 2021
Messages
89
Hello everyone,

I'm struggling to understand how to normalize my tables. Please see attached screenshot below.

Problem is my tblCause table has CauseID and Cause fields. However, certain causes might have a sub-cause which I'd like to add or remove to. How do I go about this?

Edit: What I have below makes my form/query not updatable because of tblSubCause table.


1652241058461.png
 

plog

Banishment Pending
Local time
Today, 04:45
Joined
May 11, 2011
Messages
11,613
I don't think tables with one real field of data (auto number primary keys aren't real data) should exist. Instead, you store the actual value and not the primary key in the foreign table. For example, TblEvents doesn't have ConditionId but Condition.

Extending that logic and applying it to causes and sub causes, you should ditch the tblCauses and have a tblSubcauses with these 3 fields:

SubcauseId, Subcause, Cause

Then instead of Caused in tblEvents you have SubcauseId and JOIN those 2 tables that way.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:45
Joined
Feb 28, 2001
Messages
27,001
To add to plog's statement: Making a "translation" table (e.g. tblCondition, where you translate a code into a text) is appropriate if you have to use that translation for multiple different tables. Otherwise you have "over-normalized." If your translation is only used in one place, you don't need that level of isolation, particularly for relatively short fields in that "single-field" translation table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2002
Messages
42,981
I don't think tables with one real field of data (auto number primary keys aren't real data) should exist.
I don't disagree in principle but if you don't use an autonumber as a PK, you probably need to use CascadeUpdate to allow the text value to be changed if necessary.

If you only have ONE cause, then Plog's solution works. It requires that every Cause have at least one subCause.

Another option is a m-m relationship which requires a junction table. In that case, causes and subcauses are equal and in the same table. the m-m allows you to assign multiple causes rather than just one. It also doesn't limit you to two as the current schema will.
 

cheekybuddha

AWF VIP
Local time
Today, 09:45
Joined
Jul 21, 2014
Messages
2,237
I think your tblCondition is fine.

Another way of handling your Causes is to use a self join.

Add a field for ParentCauseID.

If it is the main cause this field will be null. If it is a sub-cause it will be populated with the CauseID of the parent cause.
 

5hadow

Member
Local time
Today, 05:45
Joined
Apr 26, 2021
Messages
89
I don't disagree in principle but if you don't use an autonumber as a PK, you probably need to use CascadeUpdate to allow the text value to be changed if necessary.

If you only have ONE cause, then Plog's solution works. It requires that every Cause have at least one subCause.

Another option is a m-m relationship which requires a junction table. In that case, causes and subcauses are equal and in the same table. the m-m allows you to assign multiple causes rather than just one. It also doesn't limit you to two as the current schema will.
Do you mean something like this?

1652304056286.png



Edit:

This might be better:

1652304400041.png


Edit 2:

I've included a file if anyone wants to help with this.

Edit 3:

Sigh..... Well I'm thinking my latest iteration is better.

1652306864388.png
 

Attachments

  • Sample.accdb
    1.1 MB · Views: 117
Last edited:

5hadow

Member
Local time
Today, 05:45
Joined
Apr 26, 2021
Messages
89
I think your tblCondition is fine.

Another way of handling your Causes is to use a self join.

Add a field for ParentCauseID.

If it is the main cause this field will be null. If it is a sub-cause it will be populated with the CauseID of the parent cause.
I think I get what you're saying. But before I attempt I should clarify. I'm trying to do a hierarchy for causes. So for example, one of my causes could be "Personnel". This is the main cause which can also have a list of different personnel (AVN, AVS, ACS, OTHER).
So if I selct "Personnel" as my cause, I also would like to see other options.
I also want to have an ability to add new sub causes to main cause.
What's the best way to make these relationships?
 

LarryE

Active member
Local time
Today, 02:45
Joined
Aug 18, 2021
Messages
562
In my opinion, if you want the ability to select different causes and subcauses, then they should be a field in the TblEventCause table. You don't need separate tables for those. Then the EventID field is linked and that's all you need. When you create the input form for Event Causes, use a combobox control for both PrimaryCause and SubCauses. Users can then either select from a list or input new causes and subcauses. See attached.
 

Attachments

  • Aircraft.accdb
    1.1 MB · Views: 112

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:45
Joined
Feb 19, 2002
Messages
42,981
None of your attempts represent a m-m correctly.

There is a table of causes. This is just the definition of all possible causes. Then the m-m is a table that joins events to causes.
tblCause
CauseID (PK)
CauseDesc

tblEventCauses
EventID (PK field1, FK to tblEvents)
CauseID (PK field2, FK to tblCause)
CauseLevel (primary or other)

this gives you the ability to assign an infinite number of causes. Not just one or two and you can add an indication of whether the cause was primary or not.
 

mike60smart

Registered User.
Local time
Today, 09:45
Joined
Aug 6, 2017
Messages
1,899
I think I get what you're saying. But before I attempt I should clarify. I'm trying to do a hierarchy for causes. So for example, one of my causes could be "Personnel". This is the main cause which can also have a list of different personnel (AVN, AVS, ACS, OTHER).
So if I selct "Personnel" as my cause, I also would like to see other options.
I also want to have an ability to add new sub causes to main cause.
What's the best way to make these relationships?
Hi
If you select Personnel using a Combobox do you only want to see associated SubCauses in a 2nd Combobox

ie
Select Personnel and only see AVN, AVS, ACS, OTHER
Select Supply and only see values associated with Supply ?

If this is the case then you need what is known as "Cascading Combobox's"
 

Users who are viewing this thread

Top Bottom