0

Creating many to many relationship between two tables

I am trying to create a many to many relationship between two tables. The database I am working on has two tables one is movies and the other is actors. Each movie can have multiple actors and each actor can Be in many movies. If I leave it as a Many to 1 A.k.a. N: to 1 Relationship, I can only use the actors name once, That is, for only one movie. I have tried creating N:to 1 in the movies Database and 1 to N: in the same table to create a field in The actors Table. Of course this creates to set up tables in each table.Can anybody suggest a way to create a N: to N: relationship A.k.a. many to many relationship?

19 replies

null
    • Ninox developer
    • Fabio
    • 5 yrs ago
    • Reported - view

    Hello. Have a look here: there is a model created by Ninox support to show how you can create a N:M-Relationship:

     

    https://www.dropbox.com/s/5j1m157jior0qtf/19_Many%20to%20Many%20Table%20Relation.ninox?dl=0

     

    or here in the manual:

     

    https://ninoxdb.de/en/manual/tables/table-references-and-relations

     

    Fabio

    • Wireditpro
    • 5 yrs ago
    • Reported - view

     Thanks! I will definitely check that out. I did not originally see this. 

    • Wireditpro
    • 5 yrs ago
    • Reported - view

    This was a huge help. I am not sure if it will completely work as I hoped But I think I can get it too. Thank you so much!

    • Ninox developer
    • Fabio
    • 5 yrs ago
    • Reported - view

    You're welcome!

    • Mobi
    • 5 yrs ago
    • Reported - view

    The 19_Many MN Relationship table shows data in the fields. How do you get it to automatically populate the data? When I do this, my fields are empty and I have to manually populate the fields. But I want it to automatically and dynamically update / add data to show in the fields as I change the referenced data. What step am I missing?

    • Database Consultant
    • Kirkness_Associates_Ju
    • 5 yrs ago
    • Reported - view

    You may also find my blog posts about relationships useful (Relating to Relational Databases parts 1 and 2):

     

    software.kirknessassociates.com/blog.html

     

    To answer the question about automatically populating data when you select a linked record (I assume you mean something like picking up the price of a product from a related table?) then there are two ways:

     

    1. you can use a formula field (formula something like Product.Price)

    2. you have a normal number field and an after update trigger on the reference field ('Inserted Price' := Product.Price)

     

    Which to choose?

    The first option will alwaus display the current price of the product (in my example) which may or may not be what you want - for example with an invoice line you would probably want the price at the time (in which case use option 2).

     

    The second option has the advantage that it captures the value at a point in time - and also the value can be edited if you want. In the incoice line example you could choose to override the price for a particular sale for example.

     

    Hope this helps!

    • Mobi
    • 5 yrs ago
    • Reported - view

    Julian, thanks so much for your answer and blog post! I’m new to Ninox so it will take me a little while to process the info to see if it works for my situation, but I’m not entirely sure if I explained my situation clearly:

    I have table 1 (books) and table 2 (genres). Each book can have multiple genres and each genre can have multiple books associated with it.

    I create table 3 (for N:M relationship) and in table 3, I link to table 1 and 2. After that, when I look at table 3, there are two BLANK fields and nothing else—one for Books and one for Genres.

    If I click on the Book field, I can choose from the book titles from table 1. If I click on the Genre field, I can choose from the list of genres from table 2.

    I know I can manually select/link each book title from table 1 to table 3. But I have a long list of book titles and it will grow over time. Same with genres. How can I get table 3 to automatically populate with all the book titles and all the genres?

    The 19_Many to Many sample table linked to by Fabio Ferrara shows the MN table as being fully populated, but I can’t find out how it got there.

    Julian, I’m hoping that your solution will solve my issue, but I suspect that maybe you’re talking about a situation where I already have the book titles and want to pull in additional data like price?

    Thanks again for your help on this!!

    • Mobi
    • 5 yrs ago
    • Reported - view

    Ah, I see that I’m asking the wrong question. I’ve figured out that I have to go to Books and add each book & genre from the Books table, not the NM table.

    Julian, you say in your blog post to hide junction tables to see the data of the other tables directly. How do you hide the junction table? Viewing through the junction table is seriously awkward (at least the way I’m doing it :).

    Thank you SO much for your help!!

    • Database Consultant
    • Kirkness_Associates_Ju
    • 5 yrs ago
    • Reported - view

    I would usually hide junction tables (using the Hiden tickbox in the table definition).

    • Mobi
    • 5 yrs ago
    • Reported - view

    Thanks so much! It works now! Much appreciated.

    • Andrew.3
    • 4 yrs ago
    • Reported - view

    I would like to look at the https://www.dropbox.com/s/5j1m157jior0qtf/19_Many%20to%20Many%20Table%20Relation.ninox?dl=0 example, but dropbox says the file is no longer available to download?

    Thank you

    • Ninox developer
    • Fabio
    • 4 yrs ago
    • Reported - view

    Try now.

    • Andrew.3
    • 4 yrs ago
    • Reported - view

    Thank you very much, Fabio.

    • Mike_N
    • 4 yrs ago
    • Reported - view

    I have to say that the example could be a lot better. While it describes the basic database structure, it doesn't address how you would set up the fields or views in order to make working with this type of structure easy. For instance, if you are in the students table/view, and you want to add a course, you should be presented with a list of courses that you can pick from. When you choose the course you get a new entry in the N:M table. And same thing the other direction, when you are in the courses table you should be able to add a student by picking from a list of students. So how do you do that?

    In addition, you shouldn't be able to duplicate entries (a course shoudn't have the same student in it multiple times). I'm new to Ninox but in other databases you would ensure this by setting a unique primary key comprised of a combination of the keys from both students and courses. How would you do this in Ninox?

    And, in the N:M table, the relationship fields aren't marked as required, shouldn't they be? You wouldn't want an entry that doesn't have both because it would be invalid. Was this just an oversight because the example was put together very quickly, or is there a reason these weren't marked as required fields?

    And lastly, how would you represent composition in such a table? If a student is deleted, you would want all the entries in the MN table related to that student deleted, and likewise same in the other direction.

    Could be there's a better example or one of the templates provides more detail, but I'm trying to figure out how to implement this (I have lots of many-many relationships).

    • Guillermo
    • 4 yrs ago
    • Reported - view

    Has you finded any solution Mike N.? Its exactly what im looking for :S

    • Consultant and developer
    • Javier
    • 1 yr ago
    • Reported - view

    Any news on how to solve the questions Mike N. asked? I'm worried because it's really easy to duplicate relationships between two tables using this simple approach.

      • Fred
      • 1 yr ago
      • Reported - view

      Javier One way would be to use a button that would check the M:M table before entering the new data. You can use a dashboard that has a reference field to the other two tables and then have a button that checks to see if a record already exists and if not then enters a new record with the new links.

      Another way is if you can automate the creation of the M:M records then you can be sure that no duplicates exist. In my DB, I have code in the Trigger after update of two different fields that creates records for me in other M:M tables so I don't have to do anything. And I can be sure not duplicates exist since no one (just me) creates records in the M:M tables.

      You can also create a field in the M:M that checks the table for duplicates and then you can sort and delete duplicates. But this is after the creation so I don't think you would be interested in this solution.

      • Consultant and developer
      • Javier
      • 1 yr ago
      • Reported - view

      Fred Hi Fred, do you have any sample database with scenario in which the record creation is only possible if there is no relationship already? And for the Student deletion that Mike N.  mentioned, I suppose you need to:

      1. Avoid the "delete record" button and use a custom button for Student deleteion
      2. Attach an script that deletes all entries in the M:M table for the deleted student in step 1

      So, can we hide or disable the default add/delete record buttons in Ninox interface?

      Thanks again for you support.

      • Fred
      • 1 yr ago
      • Reported - view

      I've mocked something up. You only need to focus on the last four tables. The other tables are for another process.

      If you go to the Results table you can click on the button and see what happens. If there is already a record in TeamResults it will not enter in a new one. If there isn't then it will.

      let t := this;
      let teamcheck := (select TeamResults)[Locations = t.Location and Teams = t.Team];
      if cnt(teamcheck) = 0 then
          let newRec := (create TeamResults);
          newRec.(
              Locations := t.Location;
              Teams := t.Team
          );
          alert("New Record Created")
      else
          alert("Record Already There")
      end
      

      The first line gathers the data from the current record.

      Line 2 then looks for any records in the table we want to add a new record to and finds any records where the two other tables match (in this example Location and Team).

      Line 3 - 12 is an if then statement that says if the count of Line 2 is 0 (meaning we didn't find any similar records) then create a new record that sets the appropriate reference links in the new record.

      The alerts need to be removed if this automated.

Content aside

  • 1 yr agoLast active
  • 19Replies
  • 6638Views
  • 3 Following