While designing your application you probably encountered a need for many-to-many relationships. Simplest example is: a Person speaks many Languages while Language have many speakers (Persons). Having Persons and Languages tables ready, to implement such a relationship you would need an additional “link” table, say Person’s Languages that will refer to a Person as (one)Person – (many)Person’s Languages and to a Language as (one)Language – (many)Person’s Languages.
There is nothing complex in database structure, but adding languages to a person becomes a hassle – the user should add person’s record first, then click New in person languages section, select a language, save, click New again…
The idea is to masquerade link table and provide simple way to display and edit the data via virtual Multi-Reference Column.
In edit mode such column will be displayed inside the form, featuring standard record picker, and Add button next to it to add the selection to the list of choices and the selection made previously beneath the picker.
In view mode the column will display comma-separated list of choices, each choice decorated as a link to a record. The column will respect access rights defined for link table – e.g. if record deletion is disable, Del button won’t appear for the choices.
To create multi-reference column select the table, choose new Create Reference Column, choose the table you want to refer to and select Many-To-Many as cardinality:
After pressing Save, TeamDesk will create the link table: Person’s Languages; two relationships: (one)Person – (many)Person’s Languages and Language as (one)Language – (many)Person’s Languages and a Multi-Reference column to Language in Persons table. The link table and relationships are visible – you can extend the table with new columns and views or modify relationships’ settings in any way you need.
Implementing many-to-many relationship using link table is a common database design pattern. You can see it in Project Management All-In-One application: a Project has many Resources and one Resource can be allocated to many Projects – for that Project Resource table is used to hold [Project, Resource] pairs. To ease the migration to multi-reference column, prior to creating link table TeamDesk will first scan your database to check whether you have a table and relationships that can be used as a link table. If it’s the case, it will prompt you to choose whether to create new table or use existing one.
Another way to create multi-reference column is to convert existing one-to-many relationship. Suppose your initial database design specifies a single language the person is speaking: that’s (one)Language-(many)Persons. If you decide to take advantage of multi-reference column you can select the reference column of one-to-many relationship and perform single-step conversion via new “Convert to Multi-Reference” button.