Tables with columns always null

2

Is it wrong to create tables with mutually exclusive columns? For example, I have the tables Texto , Trecho and Linhas , Texto and Trecho has many Linhas , the Linha table is holding the two FKs always keeping one of them null .

Is this the best approach to the situation? Maybe as a second solution could be created an intermediate table Linhas which is referenced by Linha , Texto and Trecho thus saving a field in the Linha table, which is by far the most voluminous, but as counterpoint making search operations difficult.

Which of these, or even other forms, should be used?

    
asked by anonymous 06.03.2015 / 12:59

1 answer

4

Strictly speaking in fact mutually exclusive columns are not desirable and can rather be considered wrong.

This is not to say that you should never do it and that the proper results can not be obtained if you do it "wrong."

It's not always worth it to complicate modeling because of a detail to say you did the "right". Rule by rule is never good. The rule must serve a purpose. If a lot of experienced people, perhaps supported by studies follow a rule, there must be a good reason and if you can do the same without creating difficulties, do it, but do not blindly analyze if it will not have a cost.

Separating data into other tables, normalizing has a cost of development and processing to bring the information as often as it needs. You need to compensate to make this decision.

A question you have to ask in order to find another solution if the columns are mutually exclusive is what happens if you use the same column for the two data? In some cases this can become a problem, it may hamper development, it may require an extra field to tell what is registered there. But it can make up for it. It is not the most correct solution, but it is valid in some cases.

Another question that needs to be asked is what is the problem you will have for having nulls. There is a current that says no column should have nulls. This is radical, it seems academicism to me. But it makes some sense. I think null is a perfectly valid value. I do not like it much, I prefer to avoid it, but I would not rule it out, it might make it easier.

Note that in almost every database (probably all mainstream ) an unused (zero) column takes up very little space - zero or one byte - then no worry about the busy space that today is no problem anymore. The organization of the data is more important.

How much will it hurt the performance and development of your queries? Will the volume of queries be too large? Is this loss acceptable? Often it is, others are not. Conscious Denormalization is a useful tool. It should not be abused, it rarely brings such important results, but it should not be discarded because it is not the most correct.

Only you, in your specific case, can say which is better in this case. And get ready to have to change in the future. What can serve well one day may not serve in another. And this is one reason to use DRY and have canonical codes to access the database. So you need to change in one place when modeling changes.

The most important thing you are doing, you are questioning which is the best. If you still do not have much experience you have a chance to make a mistake but it will be a gain in some way. In the next one maybe not more wrong. Or maybe he makes mistakes because he did not realize that the next situation is a little different. It's part of the learning process.

But if you want an "objective" response that is a bit better than a guy or a crown, normalize, implement the alternative you are suggesting. But think about what is right from the semantic point of view, worry less about the mechanism. Try to see if the model asks to be normalized, if it is natural to have this column separated.

    
06.03.2015 / 13:51