Many to Many relationship implementation


I have a requirement to implement Many to Many relationship in database. How best is to implement this? I have gone through the below article and it was mentioned that just to attain one to many relationship in database and then Appian will handle the sitution. So, Need some clarity and suggestions from implementation experience. 


  Discussion posts and replies are publicly visible

  • It is clearly explained in the documentation link you shared. Not sure what exactly you are looking for ?
  • For these types of scenarios I usually find it safer to just make 3 separate tables and handle their relationship entirely in the DB. Let's look at the existing example of employees and projects. You would make one table for employees, one for projects, and an additional mapping table. The mapping table will have at least 3 columns, a primary key, a foreign key to the employee table, and a foreign key to the projects table. These should be enforced through foreign key constraints. You can then make a CDT for each table.

    I also recommend making views that join through the mapping table. For example, let's say you wanted to get all the projects for a given employee without a view. You could query the mapping table by employee ID, which would yield a list of project IDs. Then you could query the project table by these IDs. This would require two queries though. A better method would be to join these tables together through the mapping table, and query this view by employee. You will also need to make a CDT for this view.
  • Please follow the documentation for building up Many-to-Many Relationships.
    Some points to remember is :
    1. Proper mapping of relationships is being done. (Primary and Foreign Key).
    2. Have well defined primary key column instead of allowing allowing appian to create some random primary key column.
    3. Also considerations need to taken in case of update and deletion of data. (CASCADE or RESTRICT).
    4. Incase you are writing to all the tables, i will suggest using of a nested CDT to write data instead of many write to datastore entity nodes. So, it would make less database calls and will be more efficient.

    I hope these points help you for your implementation.
  • This is basically the only way to do it if you expect data to change. Otherwise, you won't be able to delete one employee without deleting all of his projects, and then deleting all of the employees associated with those projects, and then all of the projects associated with those employees, and all employees associated with those, on and on until all or nearly all of your database is gone.

    Or you could delete one employee, all of his references in the mapping table, and you're done.
  • 0
    Certified Associate Developer

    how to retrieve the values  in the  record to display ,  using  records?