The Many to Many Evolution (1 Viewer)

[Thales750]

Over the years as our systems have become more comprehensive, I find that Many to Many Relationships have replaced One to Many.

This isn't one of my heretical statements designed start major debate. It is simply that traditional applications of One to Many do not accurately represent real world work flows.

But, not just Many to Many Records, but Many to Many Tables as well. For that I use joining Table that I refer to as Universal Table.

What do y'all do to evolve passed the One to Many limitations.?

 

[The_Doc_Man]

I don't know that the frequency of m/m relationships have increased over 1/m and m/1 cases. I have no empirical evidence of that. Perhaps, though, this increase you discuss is that you more easily see that a given pair of tables has a more complex relationship than 1/m or m/1 - i.e. the problems haven't changed - but your understanding has grown. (Which, by the way, I count as a really GOOD thing.)

 

[CJ_London]

What do y'all do to evolve passed the One to Many limitations.?

use a many to many table,

perhaps you can provide some examples of relationships that have evolved from 12m to m2m, I personally can't think of any other than where a 12m relationship was incorrectly applied.

 

[gemma-the-husky]

I can't think how a relational database can handle a many-to-many relationship without manipulating it into 2 1-to-many relationships. I would be interested to see a candidate many-to-many.

 

[Pat Hartman]

I find that Many to Many Relationships have replaced One to Many.

It is more likely that your understanding of relationships has matured and now you are seeing them for what they always should have been. An example of this might be the relationship between parents and children. This relationship has ALWAYS been m-m because a parent might have 0,1, or many children. A child in this context would always have a birth mother and a sperm donor (who may be unknown) but in the past the nuclear family was solid and so, many developers created this relationship incorrectly by adding both Mother and Father fields to the child record rather than creating the junction table to implement the relationship correctly. Now that we have more divorce, more adoption, more single-sex marriages, etc or children with no parents at all, just guardians you might think this relationship has changed but it really hasn't. You just understand it now.

I can't think how a relational database can handle a many-to-many relationship without manipulating it into 2 1-to-many relationships.

In a relational database, there is no direct way to implement m-m. The solution uses a junction table with two 1-m relationships to solve the problem.

Candidate m=m would be:
Parents-Children
Teachers-Classes
Classes-Students
Parts-Orders
People-BankAccounts
Customers-GroceryStores

The world is full of them.

It is 1-1 relationships that are truly rare.

 

[Thales750]

It is 1-1 relationships that are truly rare.

Can you think of any examples?

 

[MajP]

Can you think of any examples?

There are artificial 1-1 maybe for security, system design, or size purpose. In theory if it is a 1-1 then it could all be in one table, because that information would uniquely identify an entity. In reality there may be reasons not to. For example If some personnel data is is maintained on another system or not everyone has access to it then there may be a 1 to 1 to avoid putting all data into a single table although it is all unique to a single individual. This is a real 1-1 since the PK in both could be the same PK and you are relating PK to PK

Another common one is a 1-M that you allow only 1 record on the M side. I call these Assignment relations. So this is in truth a 1 to many, but people will call it a 1-1. Example you have Employees and Desks. You can assign an employee to 1 and only one desk. In this case the FK is in either the employee table or the desk table. In either case you can index the FK so there are no duplicates. Although procedurally this is a 1-1 structurally it is really a 1-M with a constraint.

Another reason may be size. imagine you have lots of records and 10 percent of those times you need to record a bunch of extra information. You could have a bunch of blank fields for 90% of the records or set up a true 1 to 1 for this extended information relating PK to PK in the extended table.

 

[The_Doc_Man]

In the Navy personnel database, there were many 1-1 tables where the data in the "secondary" table involved data covered by HIPAA (USA medical privacy laws) so you needed to be cleared to see that table.

There were also tables with 1-1 for supplemental assignments but, as MajP so correctly pointed out, it was possible to use 1-M tables with a constraint since it was possible for a person to not be assigned at the moment yet we needed to be able to see that person in queries that listed assignments - obviously via LEFT JOIN syntax.

As a practical example to supplement MajP's example,...

In the Navy you can have only one person in a listed billet and one person can only occupy one billet at a time - but this was actually a case where the two tables didn't have the same PK. So instead, we cross-linked them such that they COULD be like 1-1 - but the actual linkage was 1-M based on the constraints because "M" can also be 0 (if the person isn't assigned or the billet isn't filled).

 

[gemma-the-husky]

In strict data terms there is no need for a 1 to 1. The construct is used to deal with some real world issues.

 

[Thales750]

Here are two examples of Many to Many that have the added bonus of being Many Tables to Many Tables as well as Many to Many Records.

In both cases the lower table has been expanded to display the second Connection to the Primary Table. The Field "xxTableID" has the Table Number for that Table stored in every record. The Field xxUserID gives each User a different Sort if needed.

These are not actual Queries, they are relationship diagrams I use in the Relationships Form to simplify complex data structures.

If you want Many Tables on the Right Side you need a Field for the Table Number.

 

[Pat Hartman]

This is not a standard implementation of m-m since it does not allow RI to be enforced.

 

[Thales750]

This is not a standard implementation of m-m since it does not allow RI to be enforced.

I totally agree, this is a huge step up from that. This allows real world connections. Referential Integrity is limited anyway.
Databases should allow NO deletions. The idea that Users are entering data directly into tables, even in Forms should be limited.

In the earlier discussion about error checking. I used too broad of a term. I use programming extensively to control User behavior, including what can be deleted. Which is actually nothing.
No deletions, all data is kept, all data is tracked, no exceptions. Well almost none.

 

[The_Doc_Man]

The other part of RI, though, is prevention of orphans in parent/child relationships. Preventing deletions will stop creating an orphan by deleting a parent, but you have to also prevent direct creation of orphans. Relationships.... just as tricky in the computer as they are in real life.

 

[gemma-the-husky]

@Thales750

The problem with trying to link A - B with a many to many join, is that you get a cross product result.

If A is students, and B is classes you have no way to model which classes a given student takes, or which students are in a given class.
How do you manage that in your many to many scenario?

 

[Pat Hartman]

I totally agree, this is a huge step up from that.

We're going to have to disagree about that.

 

[Thales750]

We're going to have to disagree about that.

Yeah, I don't see the same universe as most people. You are not seeing the value of this structure. I've been using it for a decade. It is extremely adaptable to all kinds of real world scenarios.

One of the things I've noticed about life, is that so called experts are very often stuck in the "we've been doing it that way for 30 years culture".

 

[Thales750]

@Thales750

The problem with trying to link A - B with a many to many join, is that you get a cross product result.

If A is students, and B is classes you have no way to model which classes a given student takes, or which students are in a given class.
How do you manage that in your many to many scenario?

Can you elaborate please?

 

[Thales750]

The other part of RI, though, is prevention of orphans in parent/child relationships. Preventing deletions will stop creating an orphan by deleting a parent, but you have to also prevent direct creation of orphans. Relationships.... just as tricky in the computer as they are in real life.

Back to the error code conversation, where I claimed to not use error code. it became obvious in that conversation that people were lumping user input control with error checking. To set the record straight, on a scale of 1 -10 on controlling input, I'm 11.5
I just don't often really on built in RI. I believe all major data entry should be in disconnected forms, and at the end queries should create new records.

 

[MajP]

I am not totally following this structure, but I think it is very similar to this conversation

Fresh Start :) Best way to handle Note fields

Hi I renamed the ID Fields I recreated the Subform I colour Coded all of the Linking Fields and these should be hidden from the user. You do not need the TableID field in both tblCustomers & tblJunctionNotes This is the standard layout. Not understanding what you mean by Multi Parent? Can...

www.access-programmers.co.uk

If so I do agree you likely need to wrap your inputs in a transaction, which in fact can be done on a form RS.
If not familiar this is a good read

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

 

[Thales750]

I am not totally following this structure, but I think it is very similar to this conversation

Fresh Start :) Best way to handle Note fields

Hi I renamed the ID Fields I recreated the Subform I colour Coded all of the Linking Fields and these should be hidden from the user. You do not need the TableID field in both tblCustomers & tblJunctionNotes This is the standard layout. Not understanding what you mean by Multi Parent? Can...

www.access-programmers.co.uk

If so I do agree you likely need to wrap your inputs in a transaction, which in fact can be done on a form RS.
If not familiar this is a good read

How to use transactions in Microsoft Access VBA

How to use transactions in Microsoft Access VBA when executing multiple queries with VBA code.

codekabinett.com

100% agree, almost all of my new record creations are transactions. They have to be when adding data to multiple tables that are dependent on other each other.

I think my Add New Customer Form has two or three pages of transaction code. The Users are not allowed to post until ever dot and cross are completely correct. I find that often the easiest way is to use virtual local tables, and Public Variables, the Construction ERP database has 278 public variables (Used as Functions) to help keep it all straight.