DB: contacts <-> types (e.g., client, donor)

Contacts can be of multiple types at the same time, such as client, volunteer, patient, donor, etc. This is not a fixed set as the number of types may be expanded later so the chosen schema has to be easily extendable.

I reduced the problem to “simple” m:n (or many-to-many) relationship as a contact can be of many types and a specific type can belong to many contacts. (Other examples: tags <-> blog posts, orders <-> products, etc.) My previous ideas were very primitive (compared to the solutions below) but they seem to have been used in production before when 3NF ¹ was not desired. I chose 3NF solution because it felt the right one (having a slight OCD) and Elixir’s Ecto (> 2.0) supports this out-of-the-box ².

I found this article about the tagging problem (through this stackoverflow answer) and it starts with the 3 main approaches.

I. Add an extra column (such as “tags”, “types”, etc.)

I overspecialized this one by trying to use an array type column or use many multiple Boolean columns, one per type. The article mentions leveraging full text search capabilities of DBMS’ and as PostgreSQL would be my choice, it is well supported.

II. Add an extra table

Simply add an extra table with a foreign key to the “main” table that contains the items to which the categories, types, tags, etc. are applied. (Probably faster but the “category” column will be redundant as hell.)

III. Normalized version of II.

Copy-pasting the query examples from the original article because even though it is now hosted on Tumblr, it seemed to have gone missing for a while.

Intersection (AND)
Query for “bookmark+webservice+semweb”
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
HAVING COUNT( b.id )=3
Union (OR)
Query for “bookmark|webservice|semweb”
SELECT b.*
FROM tagmap bt, bookmark b, tag t
WHERE bt.tag_id = t.tag_id
AND (t.name IN ('bookmark', 'webservice', 'semweb'))
AND b.id = bt.bookmark_id
GROUP BY b.id
Minus (Exclusion)
Query for “bookmark+webservice-semweb”, 
that is: bookmark AND webservice AND NOT semweb.
SELECT b. *
FROM bookmark b, tagmap bt, tag t
WHERE b.id = bt.bookmark_id
AND bt.tag_id = t.tag_id 
AND (t.name IN ('Programming', 'Algorithms'))
AND b.id NOT IN (SELECT b.id FROM bookmark b, tagmap bt, tag t WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'Python')
GROUP BY b.id
HAVING COUNT( b.id ) =2

Leaving out the HAVING COUNT leads to the Query for “bookmark|webservice-semweb”.