{"id":86,"date":"2010-10-14T08:48:48","date_gmt":"2010-10-14T13:48:48","guid":{"rendered":"http:\/\/www.teamdesk.net\/blog\/2010\/10\/multi-reference-columns\/"},"modified":"2015-10-26T14:16:58","modified_gmt":"2015-10-26T19:16:58","slug":"multi-reference-columns","status":"publish","type":"post","link":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/","title":{"rendered":"Multi-Reference Columns"},"content":{"rendered":"<p>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 &#8220;link&#8221; table, say Person&#8217;s Languages that will refer to a Person as (one)Person &#8211; (many)Person&#8217;s Languages and to a Language as (one)Language &#8211; (many)Person&#8217;s Languages.<\/p>\n<p>There is nothing complex in database structure, but adding languages to a person becomes a hassle &#8211; the user should add person&#8217;s record first, then click New in person languages section, select a language, save, click New again&#8230;<\/p>\n<p>The idea is to masquerade link table and provide simple way to display and edit the data via virtual Multi-Reference Column.<\/p>\n<p><a href=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-469\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif\" alt=\"52,1\" width=\"356\" height=\"179\" \/><\/a><\/p>\n<p><!--more--><\/p>\n<p>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.<\/p>\n<p>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 &#8211; e.g. if record deletion is disable, Del button won&#8217;t appear for the choices.<\/p>\n<p>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:<\/p>\n<p><a href=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/522.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-470\" src=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/522.png\" alt=\"52,2\" width=\"289\" height=\"161\" \/><\/a><\/p>\n<p>After pressing Save, <a href=\"http:\/\/www.teamdesk.net\">TeamDesk<\/a> will create the link table: Person&#8217;s Languages; two relationships: (one)Person &#8211; (many)Person&#8217;s Languages and Language as (one)Language &#8211; (many)Person&#8217;s Languages and a Multi-Reference column to Language in Persons table. The link table and relationships are visible \u2013 you can extend the table with new columns and views or modify relationships&#8217; settings in any way you need.<\/p>\n<p>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 \u2013 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&#8217;s the case, it will prompt you to choose whether to create new table or use existing one.<\/p>\n<p>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&#8217;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 &#8220;Convert to Multi-Reference&#8221; button.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 &#8220;link&#8221; table, say Person&#8217;s Languages that will refer to a Person as (one)Person [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-whats-new"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Multi-Reference Columns - TeamDesk Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Multi-Reference Columns - TeamDesk Blog\" \/>\n<meta property=\"og:description\" content=\"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 &#8220;link&#8221; table, say Person&#8217;s Languages that will refer to a Person as (one)Person [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/\" \/>\n<meta property=\"og:site_name\" content=\"TeamDesk Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-10-14T13:48:48+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-10-26T19:16:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif\" \/>\n<meta name=\"author\" content=\"Kirill Bondar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirill Bondar\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/\"},\"author\":{\"name\":\"Kirill Bondar\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"headline\":\"Multi-Reference Columns\",\"datePublished\":\"2010-10-14T13:48:48+00:00\",\"dateModified\":\"2015-10-26T19:16:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/\"},\"wordCount\":462,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/10\\\/521.gif\",\"articleSection\":[\"What's New\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/\",\"name\":\"Multi-Reference Columns - TeamDesk Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/10\\\/521.gif\",\"datePublished\":\"2010-10-14T13:48:48+00:00\",\"dateModified\":\"2015-10-26T19:16:58+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/10\\\/521.gif\",\"contentUrl\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/wp-content\\\/uploads\\\/2010\\\/10\\\/521.gif\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/whats-new\\\/multi-reference-columns\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Multi-Reference Columns\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/\",\"name\":\"TeamDesk Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/#\\\/schema\\\/person\\\/22c4c05bd657513c8b00122fa364c8d2\",\"name\":\"Kirill Bondar\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g\",\"caption\":\"Kirill Bondar\"},\"url\":\"https:\\\/\\\/www.teamdesk.net\\\/blog\\\/author\\\/kirill-bondar\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Multi-Reference Columns - TeamDesk Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/","og_locale":"en_US","og_type":"article","og_title":"Multi-Reference Columns - TeamDesk Blog","og_description":"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 &#8220;link&#8221; table, say Person&#8217;s Languages that will refer to a Person as (one)Person [&hellip;]","og_url":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/","og_site_name":"TeamDesk Blog","article_published_time":"2010-10-14T13:48:48+00:00","article_modified_time":"2015-10-26T19:16:58+00:00","og_image":[{"url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif","type":"","width":"","height":""}],"author":"Kirill Bondar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirill Bondar","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#article","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/"},"author":{"name":"Kirill Bondar","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"headline":"Multi-Reference Columns","datePublished":"2010-10-14T13:48:48+00:00","dateModified":"2015-10-26T19:16:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/"},"wordCount":462,"commentCount":0,"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif","articleSection":["What's New"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/","url":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/","name":"Multi-Reference Columns - TeamDesk Blog","isPartOf":{"@id":"https:\/\/www.teamdesk.net\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#primaryimage"},"image":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#primaryimage"},"thumbnailUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif","datePublished":"2010-10-14T13:48:48+00:00","dateModified":"2015-10-26T19:16:58+00:00","author":{"@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2"},"breadcrumb":{"@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#primaryimage","url":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif","contentUrl":"https:\/\/www.teamdesk.net\/blog\/wp-content\/uploads\/2010\/10\/521.gif"},{"@type":"BreadcrumbList","@id":"https:\/\/www.teamdesk.net\/blog\/whats-new\/multi-reference-columns\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.teamdesk.net\/blog\/"},{"@type":"ListItem","position":2,"name":"Multi-Reference Columns"}]},{"@type":"WebSite","@id":"https:\/\/www.teamdesk.net\/blog\/#website","url":"https:\/\/www.teamdesk.net\/blog\/","name":"TeamDesk Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.teamdesk.net\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.teamdesk.net\/blog\/#\/schema\/person\/22c4c05bd657513c8b00122fa364c8d2","name":"Kirill Bondar","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dc5bc844095b5753ccc73c589c028bf16615674f289668146bbd59205a08a52d?s=96&d=mm&r=g","caption":"Kirill Bondar"},"url":"https:\/\/www.teamdesk.net\/blog\/author\/kirill-bondar\/"}]}},"_links":{"self":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":1,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"predecessor-version":[{"id":471,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/posts\/86\/revisions\/471"}],"wp:attachment":[{"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.teamdesk.net\/blog\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}