0

I've been through several posts now trying to find out whether this is a good or bad practice.

This discussion states that it would be a violation to the database design, but I don't know how to prevent this, probably due the lack of knowledge.

The model that I am trying to build is to store laws from my country. All of them bear the same structure with one exception, that would be the paragraph that I must specify whether it is unique or not.

In here another question arouses, that is due the text in the law device. Since I'll be storing several item texts, should I add it in one table, something similar to a law_device_log, or should I spread it across tables? My concern here is due the size and the performance to search later. The number of log can easily go over millions and I have to search over text later.

My current structure, which was why I was asking about the polymorphism, is incomplete still, but is the image below:

relationship diagram

The artigo, paragrafo, inciso, alinea and item are all dispositivos (law devices), they all carry the same information, even the log, rubrica and pena as you can see, linked to the dispositivo atm.

Which approach should I use in here for this kind of scenario? Is the polymorphism the best approach or should I use an intermediate table for all of them?

7
  • Please provide some of the important SELECT statements, even if they are tentative. – Rick James Jun 7 at 1:25
  • Hello @RickJames, I am not in the point yet, my problem is structural. I am reading several posts and articles to know if this polymorphic structure is something to be used without impacting performance or if I should use a relation table for the kind of structure that I've mentioned. The numbers can go over million easily so I am worried about the performance that it might have. – André Fróes Jun 7 at 2:17
  • I believe that attempting to write the SELECTs helps to point out good/bad points of the schema structure. Polymorphism adds complexity to SQL, especially the Selects. – Rick James Jun 7 at 18:52
  • Is each parent_id joined to the id in the same table? – Rick James Jun 7 at 18:53
  • Since it is law related, it has a tree dependency. Item will always have alinea as parent, alina -> inciso, inciso-> paragrafo/artigo, paragrafo -> artigo. – André Fróes Jun 8 at 18:57
1

If you by polymorphism refer to having a row tell you what table the related information resides in, then yes that is problematic. It will more or less force you to use procedural code to access your data.

A common approach which I don't know if it applies in your case is to use a super-type with a type classifier. The super type contains all information common to the sub-types and the sub-types refer to the super-type with a foreign key. Here is one example: supertypes-and-subtypes

In you particular case it seems that dispotivo _artigo, _inciso, _alinea and _item are identical and that _paragrafo has an additional attribute unico. Here is one possible implementation (don't know the nomenclature). Note that I separated structure from content:

CREATE TABLE dispotivo_XXX
( dispotivo_id int not null primary key -- id is to vauge
, criado_por int not null
, data_criacao timestamp not null
, ultima...
, dispotivo_type int not null
-- , unique(dispotivo_type, dispotivo_id) can be used for type-safety of subclasses 
);

CREATE TABLE dispotivo_structure
( dispotivo_id int not null primary key
, dispotivo_parent_id int not null
, foreign key (dispotivo_parent_id) references dispotivo_XXX (dispotivo_id)
, foreign key (dispotivo_id) references dispotivo_XXX (dispotivo_id)
);

CREATE TABLE dispotivo_paragrafo
( dispotivo_id int not null primary key
, unico tinyint not null
, foreign key (dispotivo_id) references dispotivo_XXX (dispotivo_id)
);
4
  • Thanks for the great answer @Lennart, so putting a type here would be enough then. But the structure itself arouses another question, wouldn't the table paragrafo be a unnecessary table for just that option at the en then? I mean, the unico field. In this scenario I would just put it in the dispositivo itself and ignore the field when not being used – André Fróes Jun 8 at 18:59
  • And regarding the performance for the log table, how big must a table be so it will have toubles leading with performance when searching text? This is Brazil's contitution, this is the 4th biggest law content that we have, bear in mind that each paragraph will be a log and every future change will also be stored in this log table. – André Fróes Jun 8 at 19:01
  • I tend to start with a model that is as logical as possible, and if need be, denormalise it. You could add a view on top of those tables if you want to simplify access. However the information you have in a comment to Rick changes the scenario, given that information I would have suggested a different model. Im on my phone now, but I may extend my answer later on. – Lennart Jun 8 at 19:14
  • Your question about performance is very difficult to answer without more details. It will depend on a lot of things – Lennart Jun 8 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.