Data Normalisation, Table Design and Relationship

[Peter Peterson]

Hi,
I am having trouble understanding how to resolve a data normalisation, table design and relationship for the following scenario.
I have numerous clients that I provide business advice to, thus I keep details associate with my clients, e.g. first name, last name, address, age, gender, etc.
I also keep a record of the barriers they face within business, e.g. business skills, confidence, finance, networks, etc.
I have built a table with all the above in one Client Table - rightly or wrongly.

I also keep some data about our meets, e.g. date, meeting type (i.e. in person, email, phone, zoom) in a Meetings Table, noting there may by multiple meetings for a Client.

I now now need to keep records of what resources I have referred a Client to at each Meeting. This is the core of my problem.
I have a list of resources I can refer clients to e.g. Local Government, Dept. of Agriculture, Chamber of Commerce, Industry Association, Accountants (in general) etc.
I will have commentary associated with each of my resources, e.g. for Chamber of Commerce, "It is recommended you make contact with your local Chamber of Commerce who can be reached here www.mylocalchamber.com.au", whereby I would include this commentary in a written report and edit the hyperlink depending upon the location of the Client.

I am wondering how I can track which resource including the commentary I have used as a referral at a specific Meeting for a specific Client?

Any guidance will be most welcome

Thanking you

Peter

 

[plog]

I think fixing your large client table will be a good exercise in understanding normalization as you go further. The big issue I see is that you are storing values that should be in a field as data being used as field names. Confidence, finance, networks, etc are values not field names and should be used as such.

So, taking your first paragraph I see the need for 2 tables:

tblClients - ClientID, first name, last name, address, etc
tblBarriers - BarrierId, ClientID,BarrierType

First, add ClientID to tblClients as an autonumber primary key (https://www.w3schools.com/sql/sql_primarykey.ASP). Then that value will be used as a foreign key (https://www.w3schools.com/sql/sql_foreignkey.asp) in tblBarriers to link the two tables. Then when you want to assign a barrier to a client you just add a new row in tblBarriers.

That's the heart and soul of normalization right there. Primary keys/foreign keys. Building tables to accomodate data verticaly (with more rows) and not horizontally (with more columns). What we created between those two tables is called a 1-many relationship. One client can have many barriers.

Next, we do the same with Meetings:

tblMeetings--MeetingID, ClientID, MeetingDate, MeetingLocation, etc

Then we do the same with Resources:

tblResources---ResourceID, MeetingID, ResourceType, ResourceNotes, etc.

From what you described that will accomplish what you want. However, looking at it, what you want initially may not be right. So, I suggest you ask questions about how will the table accomodate such and such scenario, how will the data answer this questions, etc.

 

[Peter Peterson]

Thankyou plog for replying,
I started to build the data structure in Access and realised my problem.
The relationship is a many-to-many one, i.e. clients can have many barriers and barriers can have many clients. Furthermore, meetings can result in many resources being provided to clients, and resources are provided at many meetings.
I have now incorporated junction tables as how I see the solution (see attached).
Happy for any guidance if I have this in error or it can be improved.
If correct, my next challenge is to learn how to manage these relationships with forms.
Thanking you again.
Peter

 

[plog]

In your first post you stated:

I now now need to keep records of what resources I have referred a Client to at each Meeting.

Your data does not allow that. Which might be fine, you may have been incorrect in your first post, I just wanted to point it out. Your last post is a valid table structure, it just doesn't allow you to determine which meeting you refered specific resources.

For forms you use subforms to help show one-many relationships. The main form would display just 1 record from the table on the 1 side of the relationship and then the subform would show all related records in the many side of the relationship. For a junction table this is true too. You would have a client form that shows just one client record and a subform based on tblJunctionClientBarriers which has a drop down where you can select the Barrier type that goes with that client.

 

[Pat Hartman]

You can see resources recommended for a meeting but they aren't related to barriers if you want that detail also.

 

[CJ_London]

seems to me that if barriers could be overcome by more than one resource there should be a many to many relationship between barriers and resources . Also seems to me that resources could be split between resource type and geographical area which in turn may be local, national or international

Further it may be for some barriers you need resource A AND resource B, whilst for others you only need resource A OR resource B

My point is, you are approaching this starting with the client. I think you should be starting with barriers and mapping the relationships to resources - then map to meetings and finally clients.

process flow would then be along the lines of

'at a meeting with client X barriers A and B were identified.'
'of the resources available to address these barriers, resources 1 and 2 were identified as meeting the client needs for barrier A and resource 6 for barrier B'

 

[Peter Peterson]

Thank you plog, Pat and CJ for your replies.
I particular like the CJ's response as it requires me to return to the core purpose of my job which I can then map in a database.
I shall return with a new data structure in a few days.
Thanking you all
Peter