[Article] N-M Relations

This article will discuss what N-M relations are and how to use them in Servoy.

In most cases relations are 1-N (called one-to-many relationships by some), for one master row you have multiple child rows. For example: one customer has multiple contacts.

But, in some cases you need multiple items on the left side of your relation to map multiple items on the right side of your relation. This is what we consider N-M relations (called many-to-many relationships by some).

Consider the following example involving People and Projects. Multiple people can be involved in multiple projects. Constructing a database solution allowing for multiple people to be involved in multiple projects will require creating N-M relations. So, how do you create tables for that and how to do use this in Servoy?

Easy: Create one table for People, one for Projects and the so called N-M table that defines which people are involved in which projects. Here's an overview of what the tables will look like:

[image unavailable: Schema_1]

With data in the tables they would look like this:

[image unavailable: Data]

Additionally you can use this N-M table to store additional information. For example you could store the role people have in certain projects in it as well.

How would you create an interface for this? To maintain people and projects is fairly simple, create a form on each table and you are ready to go. Let's see step by step how to create an interface that supports:

- Creation of People

- Creation of Projects

- Link Projects to people

- View People and the projects they are involved in

Step 1:

Create a new solution.

In Servoy select File-New and type in the name of this solution, for example: n_m_demo.

Create the tables.

- Press Ctrl-L to enter designer and press Ctrl-Shift-D to define fields

- Select the user_data database connection if you have done a default install of Servoy and have installed Firebird with it. If you haven't done a default installation of Servoy choose the database connection you want to store the new tables in. Create tables by clicking on the New Table button.

People table:

[image unavailable: People]

Projects table:

[image unavailable: Projects]

People_projects table:

[image unavailable: People_projects]

Step 3:

Create the data entry forms.

Create a form on People and Projects to manage People and Project data.

[image unavailable: People_form]

[image unavailable: Projects_form]

Then create a form on people_projects that will show which people are linked to which projects. To do this create a relation from people_projects to projects.

Join the Relation on projectid. Make a form that shows projectnames (related field from projects based on the relation you just created).

[image unavailable: People_projectform]

Finally create a list view on Projects that lists all project names, this form will be used to link projects to people.

[image unavailable: Projectlist]

To be able to link a project to a person (create a record in people_projects) you need to create two methods:

1. a method to show a list of all projects.

2. a method to link a selected project to a person

The first method is created on the people form and looks like this:

forms.projects_list.controller.loadAllRecords();
application.showFormInDialog(forms.projects_list, 300, 200, 400, 500, "Select the project you want to add this person to");

Link the method above to a button called, "add project to this person".

The second method is created on the persons form and looks like this:

forms.people_projects.controller.newRecord();
forms.people_projects.projectid = projectsid;
forms.people_projects.peopleid = forms.people.peopleid;
application.closeFormDialog();

Link this method to a button, "select" in the projects list view form.

That's it! You have now created an N-M relation including a user interface to use it.

If you don't have time to build the example described above you download the following Servoy file to see it in action. Simply download this file, then choose File-Repository in Servoy, click on import solution and point to the file you have just downloaded. Servoy will create a solution called n_m_demo, when you open it for the first time it will request your permission to create some sample data so you can see it in action. After creation of sample data click on "add project to this person" to create N-M records.

NOTE: In this example we use People as the base table and we add projects to people. Of course, you can also do this from within Projects or even both ways. You can also add constraints (in the table or as a method) to the system to avoid duplicate rows in the people_projects table.

You may download the N-M Demo solution here. Decompress the Zip file using a product such as StuffIt Expander or WinZip.

[image unavailable: ]

To use the demo solution, Choose File -> Repository. Then select Import Solution. Navigate to n_m_demo.servoy and choose to Open it. If prompted to replace the theme, "servoy", click the "Skip" button since you already have the default Servoy theme. Click the OK button to exit the Repository and choose File -> Open. Choose to open n_m_demo.