Tables and Relationships (1 Viewer)

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
I have five tables, one for beneficiary's biographic info and other four are for spouse and three children biographic information. The beneficiary's primary key is the foreign keys for spouse and children. And also Spouse and children have their own primary keys.

1. Am I doing correct?
2. And also I have seen in some examples in this forum, in the relationship window, connecting primary key with other foreign keys. ( tiny lines connecting tables ) and referential integrity is enforced. Is it necessary? Having a drop down in the form and set properties limit to list is not enough?
3. If relationship window connection is mandatory or necessary in RDBMS, how do I use it.

Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,358
Hi. This is just one person's humble opinion, but I think relationships and referential integrity are necessary if you want to make sure your data is accurate.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
Thank you for your quick reply. So in my case I can not say both fields are equal because the beneficiary may not have spouse and children. Thanks for your patience!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,358
Thank you for your quick reply. So in my case I can not say both fields are equal because the beneficiary may not have spouse and children. Thanks for your patience!
Hi. I'm not sure what "equality" has to do with your original question about establishing relationships and referential integrity between tables.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
Sorry, join properties in the relationship window, which option I need to select - first or second or third?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:30
Joined
May 21, 2018
Messages
8,463
I would read this
1. Am I doing correct?
If you have 5 tables for beneficiaries the answer is a No.
Beneficiary information should be in one table.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
Thank you theDBguy, but if the beneficiary is not married and or no children, data won't populate since both files in join tables are not equal,. Please correct me if I am wrong.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 28, 2001
Messages
27,001
In the U.S. Navy where I worked before I retired, we had a table for the reservists. Then we had a one-many relationship with dependents, which could be anyone who fit the requirements. We made this a one-many table because, though it is not often appreciated, one-many INCLUDES two special cases: one-one and one-none. So one-many relationships are really one-(any number). The reason I point this out is that you might be tempted to define a one-one relationship, which is a good way to miss out on persons who have no spouse or children.

I have five tables, one for beneficiary's biographic info and other four are for spouse and three children biographic information.

This is probably wrong if I take the description at face value. You should have ONE table for benificiary's data and at most one more table for ALL dependents of any variety that is legal.

Having relational integrity doesn't stop you from doing things. It just stops you from doing things in the wrong order. You can add a child to a child table as long as the parent exists in the parent table first. You can delete a parent from the parent table as long as you remove any children from the child table first. Think of it as a way for Access to have extra information to know that something wasn't done correctly or completely. This is error checking, not an impediment.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:30
Joined
Oct 29, 2018
Messages
21,358
Thank you theDBguy, but if the beneficiary is not married and or no children, data won't populate since both files in join tables are not equal,. Please correct me if I am wrong.
Hi. I think now you're talking about data entry, which has nothing to do with table relationships (as far as I know). Data entry forms can be constructed to accommodate the user requirements without affecting table relationshps.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:30
Joined
May 21, 2018
Messages
8,463
That article is extremely well written and will explain the difference between a relationship and a default join provided to the query based on creating the relationship
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
In the U.S. Navy where I worked before I retired, we had a table for the reservists. Then we had a one-many relationship with dependents, which could be anyone who fit the requirements. We made this a one-many table because, though it is not often appreciated, one-many INCLUDES two special cases: one-one and one-none. So one-many relationships are really one-(any number). The reason I point this out is that you might be tempted to define a one-one relationship, which is a good way to miss out on persons who have no spouse or children.



This is probably wrong if I take the description at face value. You should have ONE table for benificiary's data and at most one more table for ALL dependents of any variety that is legal.

Having relational integrity doesn't stop you from doing things. It just stops you from doing things in the wrong order. You can add a child to a child table as long as the parent exists in the parent table first. You can delete a parent from the parent table as long as you remove any children from the child table first. Think of it as a way for Access to have extra information to know that something wasn't done correctly or completely. This is error checking, not an impediment.
Thank you for your reply. I will keep this in mind while designing my tables. Thank you.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
Hi. I think now you're talking about data entry, which has nothing to do with table relationships (as far as I know). Data entry forms can be constructed to accommodate the user requirements without affecting table relationshps.
Thank you. Yes, I meant data entry. Thank you again.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
That article is extremely well written and will explain the difference between a relationship and a default join provided to the query based on creating the relationship
Thank you.I will read and update you. Thanks for your help.
 

gsrajan

Registered User.
Local time
Today, 05:30
Joined
Apr 22, 2014
Messages
227
That article is extremely well written and will explain the difference between a relationship and a default join provided to the query based on creating the relationship
Thank yo MajP. I had a wrong understanding about this. I was thinking it is for enforcing integrity in data entry. Rather, it is about orphaned data and cascading delete and modification. Thank you for this link. This cleared me.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:30
Joined
May 21, 2018
Messages
8,463
@isladogs wrote that (one of the moderators), it is the best description I have read.
 

Users who are viewing this thread

Top Bottom