Modeling of installments and invoices

0

I have the following tables:

CREATE TABLE projeto (
  idprojeto int not null,
  ...
);

CREATE TABLE nota_fiscal (
  idnota_fiscal int not null,
  numero int not null,
  data_emissao datetime not null,
  data_recebimento datetime not null
  ...
);

CREATE TABLE parcela(
  idparcela int not null,
  valor decimal(9,2) not null,
  numero_parcela int not null,
  idprojeto int not null,
  idnota_fiscal int not null,
  ...
);

Then a project can have many plots and a invoice may be related to more than one parcel. The value is in the parcel, which is registered together with the project, that is, on the same screen, register the project and all its parcels. Only after registering the invoice and associated the parcel with the note. The value of the note is the sum of the value of the parcels that it is associated with, however I will only know the value of the note after associating all the parcels with the note.

My question is whether this modeling is correct or works well, I do not know if this is the best way to handle the values, I thought about maybe having a value for invoice as well, but thinking of normalization I believe is not correct.

    
asked by anonymous 21.02.2017 / 18:37

1 answer

1

I suggest creating a value column for the invoice, which is the sum of all the installments, ie a denormalization . So every time you save an invoice, you will add the installments and save the total amount in the invoice.

Certainly you will need to create statistical or graphical reports to consolidate the values, or even make filters by the total value and in these cases will be simpler to implement and less expensive for the databases, as you will not have to access the plots.

    
21.02.2017 / 18:45