How to model a set of 1 to 4 predetermined values

Question

I am modeling a Mysql database where the main entities are documents. Among several other fields, these documents relate to one or more phases of a particular project.

There are 4 basic values (let’s call them A, B, C and D), and the property value phase is a set of 1 to 4 of them. For example, a document may be related only to phase A, phase A and B, phase B, C and D, etc.

I see several ways to model this, but I’m in doubt about which one would be more appropriate. The options I’ve considered:

• 4 columns "boolean" (TINYINT(1)), one for each phase.
• A single binary column with 4 bits (BIT(4)).
• A column of the type set: SET('A','B','C','D').
• Relationship table.

I will need to get/display both the list of phases of a given document and the list of documents linked to a given phase.

What would be the most appropriate structure in this case? What criteria should I take into account to make this decision?

Answer 1

Unless you’re experiencing performance issues, I would go with the relationship table, otherwise it would complicate your darlings. However, if the number of documents is too large in relation to the phases, there is a less costly alternative that still preserves the normalization of the data:

Since the number of possible sets is small (16 at most), you can create a separate table to represent a set. This table of sets would basically have an ID - maybe encoded in this binary pattern, but not necessarily - and nothing else. Then you would create a relationship table between each set and its phases (1 + 4 + 6 + 4 + 1 = 16 lines).

Already the document, this would have a field that would function as a foreign key for the table of sets. A 4-bit integer is enough. Thus each document would have a data occupying as little space as possible, the overhead would be all in the table of sets (+ a Join additional, of course).

create table fase(
  id    serial primary key,
  nome  text
);

create table conjunto(
  id   tinyint(4) primary key
);

create table fase_conjunto(
  id_fase integer,
  id_conj tinyint(4)
);

create table documento(
  id    serial primary key,
  nome  text,
  fases tinyint(4)
);

To obtain all phases of a given document:

select f.*
from documento d
  join conjunto c on d.fases = c.id
  join fase_conjunto fc on fc.id_conj = c.id
  join fase f on fc.id_fase = f.id
where d.nome = "d2f12";

And all documents of a certain phase:

select d.*
from fase f
  join fase_conjunto fc on fc.id_fase = f.id
  join conjunto c on fc.id_conj = c.id
  join documento d on d.fases = c.id
where f.nome = "Fase 1";

Example in Sqlfiddle