Modeling database table

Asked

Viewed 1,111 times

3

I am creating a database that stores an electronic invoice - "Nfe" received.

Within the items of a tax note are always linked taxes.

Table example tblProdNFe_Entrada with the table tblICMS00NFe_Entrada.

inserir a descrição da imagem aqui

I am creating the bank exactly as the object generated by the deserialization that I am doing with the following code:

    T LoadFromXMLString<T>(string xmlText)
    {
        var stringReader = new System.IO.StringReader(xmlText);
        var serializer = new XmlSerializer(typeof(T));
        return (T)serializer.Deserialize(stringReader);
    }

There are groups of ICMS, for example: ICMS00, ICMS10, ICMS30 and so on.

Each group becomes an object during deserialization.

My question is whether I should create tables in the database exactly like objects or whether I should create a table that contains all fields of all groups.

For example a table tblICMSGeral containing all groups, or a table for each group: tblICMS00NFe_Entrada , tblICMS10NFe_Entrada , tblICMS20NFe_Entrada and so on.

What’s the best way?

sql sql-server modeling
  • 1

    So it depends, do the ICMS types have the same attributes? If yes, I advise you to create only one table that stores all and create an additional field to check the type of ICMS, otherwise create a table for each, put the fields that are common to them in one and the information that are different in another so there is no redundancy according to the 1st Normal Way.

  • @Nayronmorais, in this specific case I can not leave 100 % normalized, the government changes the rules very often. For example the field pICMS that in this case is the percentage of icms, it has to stay as it is in Nfe, if it is a foreign key and I changed it, all the notes already recorded would be changed, which cannot occur, because I have to leave in the bank exactly as it is in Nfe. I don’t know how to fix this.

  • You could add the normalization only for the new data, then create different procedures to check the two tables and gradually migrate the old data, because it is good to solve the problem now, otherwise it will only increase along with your work.

  • Robss70, was the answer given good? Or have other question yet?

1 answer

1

Create a table that contains all fields of all groups, realize that there is ICMS90 with all fields, which is just in case there is an exception. Then create your ICMS tax table based on ICMS90 Group.

Browser other questions tagged sql sql-server modeling

You are not signed in. Login or sign up in order to post.