Many to many relationship but with exceptions (1 Viewer)

jaryszek · Jun 3, 2019

Hi,

i want to ask you about exceptions and model tables as many to many relationship with junction table like below:

So specific CustomerTopoSysVersion can have multiple SystemVersions and each of SystemVersion can have multiple OSes.

But i have exceptions here.
Sometimes instead of CustomerTopoSysVersion --> SystemVersions --> OperatingSystems i can have
CustomerTopoSysVersion --> OperatingSystems (not every CustomerTopoSysVersion will have SystemVersion, sometimes i have to skip it)

How do you handle with this kind of situation?
Are you using some kind of artificial string for SystemVersion like "NoMatch" in junction table or something like that or you have another solutions?

Please help,
Jacek

The_Doc_Man · Jun 3, 2019

The U.S. Navy faces a problem like that for the people in this world who have no middle name. They include "(NMN)" as the middle name in any computer records for that person so that the reader who is familiar with Navy ways will see that as (no middle name). I guess you could use "(NVN)" for (no version number)?

jaryszek · Jun 3, 2019

Thank you The_Doc_Man.

i can even leave here nulls values but i do not think it is good approach. Is it?

The problem is when i am building main form and subforms i can not see related record to subform when systemVersion is empty.

I create example in attachment.
I am adding for CustomerTopology3 Oeratingsystem like here:

as you can see junction table (operating system field) was fullfilled with nullable key for CustomerTopoSysVersionID:

what i have here is the issue that i will not see this variables when i will refresh form:

I do not why. Maybe some people here are using this method to create forms and subforms?

Please help,
Jacek

The_Doc_Man · Jun 3, 2019

I'm a little skeptical of using nulls in that context. I don't think a relationship can be established through a null field. But have to admit I have not tried it.

plog · Jun 3, 2019

Sometimes instead of CustomerTopoSysVersion --> SystemVersions --> OperatingSystems i can have
CustomerTopoSysVersion --> OperatingSystems

That doesn't make sense. Normally ( and in this case) junction tables aren't real things, unlike other tables that represent real life entities, junction tables exist to sort out data between other real entity representing tables.

Your system version table contains no real unique data unto itself. It simple connects data from Topsysversiin to Operating systems. The only reason to have data in system version is to link data in the other two tables.

System version simple acts as a traffic cop directing data between this two other tables, it doesn't add any real data itself.

isladogs · Jun 3, 2019

Jacek
As a null cannot be compared to anything including another null, you cannot use null values in a relationship to get meaningful output.
Once again I suggest you re-read the threads on nulls from earlier this year as you are largely covering ground that was thoroughly discussed before.

Cronk · Jun 3, 2019

I faced a similar problem when I was asked by a large local government authority to set up a system to manage the issue of permits for organizations/companies and individuals wanting to use public land for events or commercial activities.

Either the permit was associated with the organization which in turn had an employee/member as the contact, or with an individual in their own right.

I stored two foreign keys in the permits table, OrganizationID and ContactID and created a "dummy" organization "Not Applicable". Selecting an Organization did filter the Contacts combo, except when N/A was chosen.

jaryszek · Jun 4, 2019

Hi Guys,

thank you for your help.

Colin -
Please read this:

https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate

Foreign key can be null but in Access maybe not?

I faced a similar problem when I was asked by a large local government authority to set up a system to manage the issue of permits for organizations/companies and individuals wanting to use public land for events or commercial activities.

Either the permit was associated with the organization which in turn had an employee/member as the contact, or with an individual in their own right.

I stored two foreign keys in the permits table, OrganizationID and ContactID and created a "dummy" organization "Not Applicable". Selecting an Organization did filter the Contacts combo, except when N/A was chosen.

This is a good answer and i think the best here - i can use isntead of nullable fill TEXT string like "Not Applicable" or "NoMatch" or something like this. Later when query is used i can easy get proper results.

That doesn't make sense. Normally ( and in this case) junction tables aren't real things, unlike other tables that represent real life entities, junction tables exist to sort out data between other real entity representing tables.

Your system version table contains no real unique data unto itself. It simple connects data from Topsysversiin to Operating systems. The only reason to have data in system version is to link data in the other two tables.

System version simple acts as a traffic cop directing data between this two other tables, it doesn't add any real data itself.

This is very important what you are writing here plog.
Can we go more into details? - i am not catching this.

I have SystemVersionOS - this is my junction table.

Best,
Jacek

plog · Jun 5, 2019

Ugh. When you want to discuss technical issues, especially with people not familiar with your technical details you can't casually reference items.

Your Initial post referenced "SystemVersions" in the text and had a tblSystemVersionOS in the image. Naturally I assumed they were the same thing. Now you have an image with tblSystemVersionOS as well as tblSystemVersion. I am now thoroughly confused. No idea what you mean when you use "SystemVersion" in your explanation.

Hopefully others do and can help.

jaryszek · Jun 5, 2019

thank you plog,

no, other explanation is not needed. Cronk helped me!!!

Best,
Jacek

Many to many relationship but with exceptions (1 Viewer)

jaryszek

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

jaryszek

Registered User.

Attachments

The_Doc_Man

Immoderate Moderator

plog

Banishment Pending

isladogs

MVP / VIP

Cronk

Registered User.

jaryszek

Registered User.

Attachments

plog

Banishment Pending

jaryszek

Registered User.

Similar threads

Users who are viewing this thread