Best way to keep data that depends on a condition

Navigation
6

I hypothetically have a publicação table that has, by default, the autores attributes (derived from a relationship table), titulo , edição , editora and ano . However, depending on the type of publication (such as livro , artigo em periódico , artigo em jornal and others) there would be a need to collect additional data.

For example, if publicação is of type livro there would be a need to store quantidade de páginas and volume . However, a publicação can be generic and does not have a type.

My question is: what is the best way to handle this situation?

I thought of two possible ways:

    • Create a publicação table with the default attributes.
    • Add in publicação a column tipo
    • Create a table for each possible type of publication with its particular attributes
    • Relate these new tables to publicação and form a compound key

    • or

    • Create a publicação table and add all possible attributes of the types and make the treatment in the server-side application. (this does not seem like a good solution)

    • I have little experience in database. I believe there are other solutions. I would like to know which ones?

        
    asked by anonymous 11.07.2015 / 17:38

    3 answers

    5

    There are absolutely no right solutions in software development. So the best way depends on a lot.

    What can be said that the form considered more correct is the first to involve the normalization of data . More correct does not mean the best. There are situations where you should do what is not so correct to achieve the best outcome for the specific situation.

    In general the less "optional" information you have, the better.

    Even defining what is best is already complicated. Better in what? For what? For whom? When? Even if you meet one criterion, you will not be able to meet others.

    Alternatives exist. For example you can go deeper in normalization, go to 6a. normal form or the form of key and value pairs . I do not recommend it but it's a way to decouple the data.

    The second form should only be adopted if you have performance problems (if measured correctly), which I doubt will be the case.

    My only question is about using the composite key. Unless you have something that you did not report I guess you do not have to. Publication%% can be used as the primary key in the table of each specific type.

    If the publication is generic, just leave the column indicating the type of value, probably as null.

        
    11.07.2015 / 18:32
    2

    If you go to the second alternative the publication table will not scale (that is, if you have too many records the performance to get specific information will be bad). In addition you have this "information treatment" in the application which can also eventually become a headache. No, it's not a good solution.

    The first alternative is the best and you are actually talking about a common step in building databases, the Second Normal Form or 2FN. Data normalization is a set of following procedures for obtaining consistent storage and effective access to information. You have a lot of information on the net, starting with the wikipedia , with well explained explanations and examples. It will be good to understand these principles and then to make the best of your data model.

        
    11.07.2015 / 18:00
    1

    I think the first form would be the best, even thinking about scalability and future implementations you might need. In addition it does not "swell" the table, resulting in optimized performance.

    For cases where you need to merge everything, you can create a View in the database that returns the grouped information.

    The first form also corresponds to the standard of normalization of database, the Second Normal Form, as already mentioned by Craveiro .

    Later when you need to add other features of a type not foreseen today: Online publishing, facebook post, for example, you will have a much easier way to do this, just add one more type and adjust the View.

    That is, the first option will make your life much easier in the future.

        
    11.07.2015 / 18:46
    Javascript, action on selected text Is it recommended to use C # 6 in Visual Studio 2013?