How do I get prices of each invoices?

Question

Hi,

I am developing a back-end that process about 500 invoices a day. I have following tables in my database.

invoices
invoice_id int primary_key auto_increment
user_id varchar(10)
invoice_type enum { package, document }


users
user_id int primary_key auto_increment
rate_package_id int
rate_document_id int

rates
rate_id  int
rate_name  varchar(10)

rate_prices
price_id  int primary_key auto_increment
rate_id int
weight int
price double(8,2)

at the end of day, I have to set price for each invoices.

how do I get prices of each invoices? I actually have no ieda except addding 'rate_package_id' and 'rate_package_document_id' to invoice table so I can query prices from rate_prices directly.

Answer 1

SELECT  invoice_id, price
FROM    invoices i
JOIN    users u
ON      u.user_id = i.user_id
JOIN    rates r
ON      r.rate_id = CASE WHEN i.invoice_type = package THEN u.rate_package_id ELSE u.rate_document_id END
JOIN    rate_prices p
ON      p.rate_id = r.rate_id

Not sure where do you keep the weight of your packages.

Seems it must be it invoices, in which case just add it to the last JOIN condition.

Answer 2

I assume you would probably need a LineItems table which would be associated with an invoice, such as:

lineitem_id int primary_key auto_increment
invoice_id int
price_id int
quantity int
total_price double(8,2)

you then have a table to associate prices with invoices, each invoice can have 1 or more line items.