Issues with relationships (1 Viewer)

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Hi All,

I'm having trouble creating my database (airport database).
After charing my database with this forum concerning another topic, I was advised to rething the structure of my tables.
I'm not 100% sure I comply with the 3 rules of normalizations. The reason I not 100% sure is because when I try to create my relationship it doesn't work.

I created 2 tables : tbl_Aiport_info, tbl_Runway

For example, in the table Airport_info I have the data concerning a unique airport. In the table Runway, the data is unique for a specfic runway but a unique Airport can have multiple runways.
So, in the table Airport_info, I created a primary key (ICAO) which identifies a unique airport. In the table Runway, I created a primary key (RunwayName) which is unique, and I added a field ICAO (to be able to link the table Airport_info with the table Runway).

In addition, the field ICAO in both tables have the same proprety and the same Data type.

When I try to create a relationship between my two tables with a "one-to-many" type. I tick "enforce referential integrity, cascade update related fields and cascade delete related records". However, the relation won't work because apparently (From Access) "the data in the table Runway violates referential rules".

I've done my researche on referenial integrity rules. 3 rules existe :
1- In the parenting table, the related field must be a primary key.
2- The related fields in both tables must be of compatible data type.
3- Both tables must belong to the same database

I have the impression I comply with all 3 rules of referential integrity. However, the "one-to-many" relation won't work.

I've attached my database so you can have a look.

Thank you for your help,
Mat
 

Attachments

  • DataBase_V7_WIP.accdb
    1.7 MB · Views: 96

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Soory, I forgot the question :
Does someone have an idea why it won't work? Is it because my tables are not correctly normalized? Is it because the propreties in each field I want to link are not the same?

Thank you,
Mat
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:14
Joined
Oct 17, 2012
Messages
3,276
This is basically something of a continuation of the thread found at https://access-programmers.co.uk/forums/showthread.php?t=302715 . As I mentioned there, I'll try to take a look at this tonight. From what I saw when I looked at your database, you're going to need rather more than four tables.

Until then, I would recommend this page, it might help you get a better feel for normalization: https://www.studytonight.com/dbms/database-normalization.php
 

plog

Banishment Pending
Local time
Today, 07:14
Joined
May 11, 2011
Messages
11,613
It's because you have parentless data in tbl_Runway. You are enforcing all these rules, but that 1 record you have in tbl_Runway violates them so it can't create the rules you ask because it would immediately allow you to break them.

Delete the record in tbl_Runway.
 

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Than you,

Frothingslosh, thanks, I'll check your link and see what I can do.

plog, I not quite sure to understand. The record you want me to delete is the ICAO field in the runway table? If I do that, how can I link a unique airport to multiple runway?

Mat
 

plog

Banishment Pending
Local time
Today, 07:14
Joined
May 11, 2011
Messages
11,613
record = collection of data, a row
field = one part of a collection of data, a column

Delete the row of data in tbl_Runway. Do not delete any columns from tbl_Runway.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:14
Joined
Oct 17, 2012
Messages
3,276
To expand on what plog is saying - when tables have a one-to-many relationship between them, that is what we call a parent-child relationship. Every record on the many (child) side has to have precisely one matching record on the one (parent) side.

An example is an invoice - you have the invoice itself, and the line items on the invoice. The invoice is the parent, and the line items - whether there are 1 or 101 - are the children.

Plog is pointing out that you have records in your runway table that do not have matching records in the airport table, and the relationship cannot be created until that is remedied.
 

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Sorry plog I don't understand what you want me to delete.

I not quite sure I fully understand (well I kind of understand) "Every record on the many side has to have precisely one matching record on the parent side."

All the record in the table Runway are unique for a RunwayName (primary key). All record in the table Airport_info are unique for a specific Airport (the ICAO also a primary key). So I guess I comply with the first rule of normalization.

So to comply with the 2 rule of normalization, I added the ICAO in the table Runway. So, when choosing an airport in the table Airport_info, there are many runway related to it in the table Runway.

Also : "Plog is pointing out that you have records in your runway table that do not have matching records in the airport table". The record in the runway table refere to a unique RunwayName. And the RunwayName referes to a unique airport. So to be able to match the records from the runway table to the records in the Airport_info table, I have to add the primary key of the aiport_info table to the runway table?

Mat
 

plog

Banishment Pending
Local time
Today, 07:14
Joined
May 11, 2011
Messages
11,613
I don't think I can be more more succint and helpful than my prior post. Answer these 2 questions:

What is a record?

How many records are in the tbl_Runway table of the database you initially posted?
 

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Could you try and explain it differently please?

To answer your questions. A record are the columns I create in the table? In the runway table, I have 57 columns. Each column has a unique value that depends on the runwayName (primary key)

Mat
 

plog

Banishment Pending
Local time
Today, 07:14
Joined
May 11, 2011
Messages
11,613
Nope. I even gave you the answer above. Reread Post #6.

Open tbl_Runway and look at the bottom left corner of it--the very bottom, what does it say?
 

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
"Records are composed of fields" source : https://www.webopedia.com/TERM/R/record.html
So, the record is the table? Because the table is composed of fields right?

Bottom left :
- In design view there's : F6=Switch panes, F1=Help
- I View there's "ready".

Is that what you're trying to point out?
 

plog

Banishment Pending
Local time
Today, 07:14
Joined
May 11, 2011
Messages
11,613
A record is a row of data. A field is a column of data. You have 1 record (row) in tbl_Runway. Delete it.

Open tbl_Runway and view the data. Hit Ctrl-A and then Delete.
 

mike60smart

Registered User.
Local time
Today, 12:14
Joined
Aug 6, 2017
Messages
1,899
Hi

You need to study table normalisation.

I always apply the rule that EACH table MUST have an Primary Key and to make it an Autonumber Data Type.

So your tblAirportInfo needs to have the following changes:-

1. Remove the current Autonumber from field ICOA
2. Insert a field named AirportID and make it an Autonumber

Next

Your tblRunway needs the following changes:-

1. Remove the current Autonumber from field Runwayname
2. Insert a field named RunwayID and make it an Autonumber
3. Insert a new field below RunwayID and name it AirportID

Now go to the relationship window and create a Relationship between AirportId from tblAirportInfo and AirportID from tblRunway

Enforce Referential Integrity and you will then be able to Create a MainForm / Subform for Data Input where 1 Airport can have Many Runways
 

Mat1994

Registered User.
Local time
Tomorrow, 01:14
Joined
Nov 29, 2018
Messages
94
Thank you, it works now.
I'll always keep in mind to create a ID as a primary key and autonumber.

Mat
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:14
Joined
Oct 17, 2012
Messages
3,276
You can do it other ways, mind you, but creating an autonumbered ID field makes it really easy to keep the key away from prying user eyes, and is really easy to work with. It also makes lookup tables easy to work with.

Lookup tables are basically just lists - they're often, although not always, just two columns: an ID column, and a value column. They're generally used to populate combo boxes, so that users aren't required to fill the values in, which eliminates the possibility of typos. Just about anything you want users limited to a standard list can be used: state names, model colors, departments, you name it. They also often save space - instead of including the value over and over again, you save the ID to the main record, and join it to the lookup table. Depending on what the data is, that space savings can add up.
 

Users who are viewing this thread

Top Bottom