How to model a set of 1 to 4 default values

Question

How to model a set of 1 to 4 default values

Navigation

#1 by (4 votes)

4

I'm modeling a MySQL database where key entities are documents. Among several other fields, these documents relate to one or more phases of a given project.

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

I see several ways to model this, but I am in doubt about which would be the most appropriate. The options I considered:

4 "Boolean" columns ( TINYINT(1) ), one for each phase.
A single binary column, with 4 bits ( BIT(4) ).
A column of 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 certain phase.

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

mysql sql modelagem

asked by anonymous 19.11.2015 / 20:03

1 answer

How to remove Input image border Is there any way to go back to the last branch in git?

score 4 · Accepted Answer

Unless you are experiencing performance issues, I would go with the relationship table, otherwise it would complicate your queries a bit.

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 (maximum 16), you can create a separate table to represent a set. This set table 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).

As for the document, this would have a field that would function as a foreign key for the set table. A 4-bit integer is enough. So each document would have a data occupying as little space as possible, the overhead would all be in the sets table (+ an additional join, 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 get 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 the 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";

SQLFiddle Example