Table structure for storing invoices?

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Table structure for storing invoices?

Post by quailcreek » Fri Nov 27, 2015 10:15 pm

Hi,
I would appreciate some assistance on setting up some changes to my SQLite table structure. I currently have a Quote table which holds all of the quote information and an Invoice table which, of course, holds all of the invoice data. For the most part they both function the way I want them to. What I want to do is allow payments on the Invoices. I would like some input on how and where to store the payment and payment date? Right now I have them being saved in 2 columns in the invoice table but I’m not sure if that’s the best approach longterm. Any help would be appreciated.

Thanks,
Tom
Tom
MacBook Pro OS Mojave 10.14

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 362
Joined: Tue Apr 10, 2012 9:18 am

Re: Table structure for storing invoices?

Post by ghettocottage » Sat Nov 28, 2015 1:22 am

I am not sure if that is all the tables you have created, but from what you have mentioned so far, it sounds like a Payments table would be appropriate for what you are doing. Maybe something like:

Payments
>id
>invoice_id
>date
>amount
>payer

Unless you have a Users table, in which case you could add a users_id to the payment as well.

quailcreek
Posts: 746
Joined: Sun Feb 04, 2007 11:01 pm
Location: McKenna, WA

Re: Table structure for storing invoices?

Post by quailcreek » Sat Nov 28, 2015 1:31 am

Hi ghettocottage,
So you suggest a separate "Payments" table with INNER JOIN to the invoice table? What is the advantage of dong that?

Tom
Tom
MacBook Pro OS Mojave 10.14

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 362
Joined: Tue Apr 10, 2012 9:18 am

Re: Table structure for storing invoices?

Post by ghettocottage » Sat Nov 28, 2015 2:39 am

Just off the top of my head:

*multiple payments on a single invoice
*bringing up all payments for a particular month (or any time-span)
*displaying all payments from a particular client
*single payment for multiple invoices

Post Reply

Return to “Databases”