Jump to content



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?



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


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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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