Topic: Invoices

[clay1]

I am creating a table to store some information about invoices I am sending to clients. The table is updated when the invoice is sent.

One of the things I would like to store is which products the invoice was for, this way I can pull up a duplicate of the invoice at a later date if needed, view the product, and also for accounting purposes.

Each product has a id number

How should I go about storing this in the invoices table?

[btherl]

If the invoices table has primary key invoice_id and the products table has primary key product_id, then you can create a table

CREATE TABLE invoice_products
(
  invoice_id integer,
  product_id integer,
  product_count integer,
  cost numeric(9,2),
);

For example.  This will let you record any number of products along with an invoice, along with a count of how many products there were and the price (which can be per-product or a total)

[clay1]

So would I just have multiple records with the same invoice_id for each product?

such as:

invoice1 product123
invoice1 product234
invoice1 product566

etc?

[roopurt18]

That's the correct way to do it, yes.

[clay1]

OK.

So how do I generate the invoice_id so that it's sequential, not unique, but matches with the correct invoice?

[btherl]

invoice_id is generated by the invoice table.  You would never have the invoice_products table generating it, because all that table does is refer to items in the other two tables.

[zenlord]

I have a question regarding the data type for 'cost'.

You (btherl) said it to be numeric(9,2), but there is a data type 'money' available , as I found out this weekend. Using it has proven to be a challenge (that has not been met at this moment), but I wonder if it has advantages over your approach?

Vincent

[btherl]

Sorry, I didn't see your reply until now.  I'm not familiar with the money type myself so I can't comment.  We use numeric(9,2) at my workplace where exact money types are required.  Sometimes we need more precision (eg we may need to account for partial cents), so we use a double precision there.  That can give unexpected results occasionally, but it's ok for the situations we use it in.

[roopurt18]

@btherl
For partial cents why not use numeric(9,3) or numeric(9,4) along with arbitrary precision math libraries?

Oh.  I just saw but it's ok for the situations we use it in, which I suppose answers my question.  But I'm still replying so the OP can see alternative solutions.

[zenlord]

thanks for your reply. Since I was not able to get the money-type working (and since I learned it is deprecated anyway), I have switched to numeric(9,2). All I need it for is to store exact amounts - no calculations other than adding and subtracting need to be made, so I don't expect much trouble.

[roopurt18]

If you are adding and subtracting dollar amounts, then you should be using arbitrary precision functions like those in bcmath or gmp.

People on the business end of things can be very forgiving about a lot of things.  One thing they are not forgiving about is when the money amounts are wrong.