Design for many-to-many relationships between tables

[kgodey]

Problem

While viewing a table, a user might want to see multiple records from other tables linked to a given record.

For example, consider a table that stores information about movies, "Movie", and a table that stores information about actors, "Actor". The user may want to see all actors associated with a movie while looking at the record for the movie in "Movie" or all movies that an actor has been in while looking at that actor's record in "Actor".

Backend implementation

Since databases only link between single records, the way this would be implemented at the database level would be to create to create an intermediate table that has links to both records.

Consider the following Movie table and Actor table:

Movie

ID	Title	some other fields
1	Top Gun	...
2	True Romance	...
3	The Ghost and the Darkness	...
4	Wall Street	...
5	Interview with the Vampire	...

Actor

ID	Name	some other fields
6	Christian Slater	...
7	Val Kilmer	...
8	Tom Cruise	...
9	Michael Douglas	...

---

In order to end up with a view that can do something like:

ID	Title	some other fields	Actors
1	Top Gun	...	Tom Cruise, Val Kilmer
2	True Romance	...	Christian Slater, Val Kilmer
3	The Ghost and the Darkness	...	Val Kilmer, Michael Douglas
4	Wall Street	...	Michael Douglas
5	Interview with the Vampire	...	Tom Cruise, Christian Slater

We will need to create an intermediate table that looks like this:

Movie Actor Link

ID	Movie ID	Actor ID
1	1	7
2	1	8
3	2	6
4	2	7
5	3	7
6	3	9
7	4	9
8	5	6
9	5	8

Since this intermediate table is a one-to-one mapping, once it's created, we can also use it to create the reverse-mapping like so:

ID	Name	some other fields	Movies
5	Christian Slater	...	True Romance, Interview with the Vampire
6	Val Kilmer	...	Top Gun, True Romance, The Ghost and the Darkness
7	Tom Cruise	...	Top Gun, Interview with the Vampire
8	Michael Douglas	...	Wall Street, The Ghost and the Darkness

Proposed solution

• We should provide an easy way for the user to link multiple records to a single record. We will automatically create the intermediate table in the backend, but we need to explain to the user what's going on and why there's a new table.
• We need to ensure that the user doesn't delete the new table because they don't know what it is.
• We also need to display multiple records. Since this is not really a table functionality, we should create a view for this instead if needed. We should also do this automatically and explain to the user what is going on.

The workflow should be something like:

• The user is browsing a table or view and decides that they would like to see all the records associated with a single record in one row.
• Once they decide they would link to link multiple records, we automatically create the intermediate table and explain to the user what is going on.
  • If an intermediate table already exists, we can use that instead.
• If the user is on a view, we can add a "computed" column with multiple records to the view.
• If the user is on a table, we need to automatically create a view with a "computed" column and explain to the user what is going on.

Additional context

• Design for usage of Foreign Key constraints #243
• We will probably be reusing design from this when we do "computed" data based on formulas or functions.

[kgodey]

Closing this issue since many-to-many will be covered in the following issues:

• Design for usage of Foreign Key constraints #243
• #784