Modeling of parcels and fiscal notes

Question

Modeling of parcels and fiscal notes

Asked 7 years, 9 months ago

Viewed 250 times

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 plot. The value is in the plot, which is registered together with the project, that is, on the same screen register the project and all its plots. Only then register the invoice and associate the plot with the note. The value of the note is the sum of the value of the plots it is associated with, but I will only know the value of the note after associating all plots with the note.

My question is if this modeling is correct or works well, I do not know if this is the best way to treat the values, I thought maybe have a value for tax note too, but thinking of normalization I believe that is not correct.

I don’t think the installment should be associated with the project since installment or warning (1 installment) is a form of payments that are linked to a purchase (project). I don’t know all the details and rules of the ai project, it seems to me more appropriate 1 project -> 1 invoice -> 1 or N installments.

– rray

2017/02/21 at 17:44
I get it. Initially I thought this way, but I did so because I already have the values of the plots of the project at the time of registering it and I need to somehow know that these plots are of certain project to be able to consult.

– Fernando Zabin

2017/02/21 at 17:54
So part of the problem is that you have installments to pay or to receive without having a receipt? then in this case you associated with the project?

– rray

2017/02/21 at 18:05
Yes. I register a project and its installments with values already defined in the contract. And only in the delivery of the projects I will register the invoice, in addition has a variable, usually is paid the first installment of each project in a single note.

– Fernando Zabin

2017/02/21 at 18:43

1 answer

Browser other questions tagged database

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

by Murillo Goulart • **3,391** points · Answer 1 · 2017-02-21T17:45:47+00:00

I suggest creating a column of value for the invoice, which is the sum of all the plots, ie a denormalization. So every time you go to record a receipt, you’ll add up the installments and record the full amount on the invoice.

Surely you will need to create statistical reports or graphs to consolidate the values, or even make filters for the total value and in these cases it will be simpler to implement and less expensive for the databases, because you will not need to access the plots.