« [News] Layoffs Hit FileMaker Development Team | Main | [Article] Roll Your Own Tab Buttons »

October 12, 2004

[Article] N-M Relations

by Jan Aleman
Servoy

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:

Schema_1

With data in the tables they would look like this:

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.

Step 2:

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:

People

Projects table:

Projects

People_projects table:

People_projects

Step 3:

Create the data entry forms.

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

People_form

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).

People_projectform

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

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.

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.

Posted by David Workman on October 12, 2004 at 01:43 PM in Articles | Permalink

TrackBack

TrackBack URL for this entry:
https://www.typepad.com/services/trackback/6a00d8341c8d8153ef01b8d250332e970c

Listed below are links to weblogs that reference [Article] N-M Relations:

Comments

I wonder, If I can estimate the resulted .zip file size before compressing large folder? If I know I will not save any space then I will not spend my time for zipping! Please, advise. Thank you!

Posted by: Terry | Aug 1, 2005 7:44:36 PM

This helps and I suspected that the answer looked something like what you have here. I think the trickier piece is to create a type ahead method that allows you to limit the number of projects by starting to fill in some info.

Posted by: Chris Patteson | Jan 8, 2007 11:22:08 PM

type ahead build steps:

1- create a value list that pulls its values from the project table. display the project name and store the project id.

2- attach the value list to the project id field of table people_projects. make the display type of the field type ahead.

to use:

1- don't need the list of projects anymore to show in a form in dialog.

2- create a record in the people_projects table through a people_to_people_projects relationship (matching on people id).

3- start typing in the project id field that is the type ahead field. it will filter through available projects based on what you type. pick one and the project id will be saved to the record.

Posted by: David Workman | Jan 9, 2007 1:29:09 AM

i need this project

Posted by: d.srikanth | May 21, 2010 2:09:53 PM

Trying to download the sample files and it's a broken link. Are they located anywhere else?

Posted by: Todd | Jul 1, 2010 3:27:56 PM

The comments to this entry are closed.