Normalization - Struggles In Access (2 Viewers)

[dalski]

New to db design & really struggling with it. Watched countless tutorials; implementing seems far harder. Been told to avoid composite keys in Access as it makes hard work, which seems to cause violations but I really don't understand normalization.

• ResTypesT - Labour/ Plant/ Material/ Sub-Contractor/ Other/ Prelims
• RgsT - (ResourceGroupsTable) Records to organise resources created from the ResDetailsLibF (form)
• ResourcesLibT - resources are entered through the ResDetailsLibF

Problem
"Activites" - think of them as a resource from a resource & my predicament:

• ActvTitlesLibT - the parent record of the "Activity" itself. It will be held under RgsT (Resource Groups); though this is just for organizational purposes; do not dwell on this. The reporting of the rates themselves will fall under the ResourceType & ResourceGroup; NOT THE ACTIVITY RESOURCE GROUP.
• ActvCompositionLibT - Contains records which determine that particular "Activity's" resources.
• PROBLEM - Say a resource is changed in the ResourcesLibT; it's ResType or Rg. This change is not reflected in the ActvCompositionLibT. How do I account for this?

The forms contain nested comboboxes which refine selection of resources for the user.

 

[jdraw]

dalski,
I recommend you provide a 30,000 ft overview (simple terms no jargon) of the business/process you are trying to automate. It will help put your info into context.

 

[plog]

I understand normalization, but have no idea what your organization does, nor how this database fits into your organization. So, let's step back from the database for a second and explain 2 things to me, using a paragraph each:

1. What does your organization do? Pretend its career day at an elementary school and explain to us in simple terms what it is you do. No database jargon allowed in this paragraph.

2. What will this database help your organization do? A little database jargon allowed, but focus on its role within the organization--what data will it organize and how will its output be used?

In general, the biggest issue I see is a spiderweb of relationships. In a database there should only be 1 way to trace a path between 2 tables. In yours, from ResTypesT to ActCompositionLibT I can trace 4 paths between them. That's not right. Because I don't understand your organization I don't know which one is correct.

Additionally, I feel you might have over-normalized your tables by having too many tables. My gut is telling me at least 2 of them can be merged. Again though, not enough understanding of your organization to know though.

 

[MajP]

To help discussion, I put these as images so you do not have to pop it open to see it.
People mistakenly freak out when they see a closed loop thinking somehow you created a circular relationship. They are wrong, and do not know what a circular relationship is. It is very hard to create a circular relationship. So I modified it by adding a second instance of the table, but think once you save it Access will revert to a single table.

With second instance (technically the exact same thing), but to keep people from freaking out.

 

[MajP]

Here is the common problem.
If I have a table that hold a key to another table for example

tblActions
--Action ID
--Action Name
--Action TypeID

tblActionTypes
--ActionTypeID
--Type Description

If I have a data table
tblActionsTake
--ActionsTakenDate
--Actions ID_FK 'Relates to my actions

I do not have to pull into this table the Action Type ID. I can get that through the relation to the action table in a query.

So a RG is related to a ResType

Rin your resourceLib table you only need to hold the RGid and not the ResTypeID. That ResType (and all res type fields) would come through the relation to the RgT id.

 

[MajP]

FYI. One thing that I find helps is to call my Foreign Keys something different.
Instead of RegID in the ResourceLib I always call these RegID_FK (telling me it is a foreign key relating to the RegID). It helps a lot in viewing queries. But you did a good job giving the PK a unique name without spaces (some people just put ID).

 

[MajP]

I also see why you are struggling on that, because the form design is hard to do with a proper data structure.

You want to do a cascading combo on a continuous form, but you do not need to actually save the ResTypeID. You only need it for filtering the RgID. If you make this an unbound control then you get the problem that all unbound controls show the same value. I will demo a way to do this.

 

[dalski]

Thankyou all so much. I'm sorry I did not post all because I thought too much info deters people's interest & I think it's too big a topic for single post. Usually & people rarely understand me so I tried to simplify in OP. Looks like MajP has explained it for me which I'm going to read as soon as I post this.

1. What does your organization do? Pretend its career day at an elementary school and explain to us in simple terms what it is you do. No database jargon allowed in this paragraph.

2. What will this database help your organization do? A little database jargon allowed, but focus on its role within the organization--what data will it organize and how will its output be used?

1 - Cost Estimation of construction projects. Specific to each tender enquiry. I have used several db's relating to this & the best by far is the type of features I am trying to implement here. A Tender is a particular enquiry, inside a tender are Bills. Inside the bills are BillRows; individual items contravariantly related to the parent bill.

2 - Estimating the cost of a construction project; which is determined by the resources used (from Resources). A library is where resources exist. Their prices change as time goes on & although it looks like redudant data resources are unique to each tender; their descriptions, the units used... the user needs the freedom to change as he/ she desires on that particular Tender. I believe it to be one of the rare exceptions for redundant data.

Resource records need to be assigned to each BillRow, entered inline to each bill OR/AND via an Activity (Resources from resources/ groups...). My plan was to have near identical Resources for the Tender & copy instances from the Library, but records were needed for that particular tender should the user deviate from it's typical make-up.
I'll have queries which breakdown the cost relating to their applicable headers... This is another topic. So don't worry about this.

No doubt I have not explained myself clearly & there is quite a bit to this topic; which I only mention as you wanted a detailed breakdown.

To help discussion, I put these as images so you do not have to pop it open to see it.

 

[MajP]

I believe this is the correct Relationship, and it will make the data much easier. However, it will make the form design more challenging.

Example.
If an adding something to the Active composition library. I simply select a Resource from the Resource Library. I can show in the query the RG, ResType through the "Chain". However, you are going to want to do a lot of cascading combos (or some other way to select) and this is hard to do especially in continuous forms. I would do a more non standard design.

I would use pop up forms as my picker instead of cascading combos. Once you build one they are all slight variations. But wrestling with unbound cascading combos is an continuous form is a huge PITA.

Here is my selector that I use to do this.

I use a Pop up Form with a datasheet subform. There is code involved, but if interested you can look at what I did. You click on the pencil and it pops open the form. Nice thing is you can resize, sort, and filter all the columns because it is a datasheet. Then click on a row and hit OK and it will edit. If you open to an existing record it will move to the selected row. The popup forms are reusable and can be called from any form. It takes a little work to do this the first time, but I use this idea often and it comes in handy once you build one of these.

 

[dalski]

Thanks MajP , trying to figure it out. Anyone have any tutorials on this? What's with the Nz & 0 in the opening argument. MSN does not have e.g's like this. Is Nz a local variable not requiring declaration, & lost for what the ,0 is for.

Nz(Me.ReLibID_FK, 0)

Would there not be a lot of error handling with different forms open?

 

[MajP]

Anyone have any tutorials on this?

Like I said, I can see why this may have been confusing because of the form design that you needed.
You need to cascade, but you do not need to hold the additional foreign key. This condition occurs some times but not that often.

Bottom line if you have TableA, TableB, and TableC
If tableB has a foreign key relating to TableA and TableC has a key relating to TableB you do not need TableC to have both foreign keys.
If C relates to B and B relates to A then C relates to A through B

TableA
A_ID
A_Description

TableB
B_ID
B_Description
A_ID_FK

TableC
C_ID
C_Description
B_ID_FK

Now you can join A to B and B to C and show some or all fields from the three tables.

The way you had it can work but there is a drawback to data integrity

If TableC held both keys to A and keys to B, it is possible that you can modify one of those keys and not be in synch. Referential Integrity can not catch that. So you could get the case where you are holding two out of synch keys.
Labour (type)
Bull Dozer (resource)

Nz(Me.ReLibID_FK, 0)

I am trying to pass the current RelLibID_FK to the pop up in openArgs. The form then moves the pointer to that record in the pop up.
If I am on a New record the RelLibID_FK will be NULL.
This way I pass 0. I could pass anything that does not exist like
Nz(Me.ReLibID_FK, -1) since no RelLibID can ever be less than 1.

Since no RelLibID = 0 the find first will not find anything.

That form is a little involved, by trying to do this cascading is a real pain. I think this is more intuitive and can be reused.

Would there not be a lot of error handling with different forms open

No. But I only open pop ups as Modal. So you can only work in that form and go back to the other form only after closing the pop up. I used a continuous form on my pop up, but could have used a listbox as my selector. But then you lose the sort and filter capability. The pop up is locked (no edits, additions, deletions).

 

[MajP]

Here is a version using a Pop Up listbox. In theory you could bind the listbox directly on the form, but I find that confusing to see the selection.