Jump to content

Invoices


clay1

Recommended Posts

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?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

  • 1 month later...

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

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

THX 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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.