Thread: Invoice Table Design

Invoice Table Design

From

Robert Heinen

Date:

24 November 2016, 17:18:03

I was wondering if anyone might be able to help me out with a table design question.

A quick intro -- I'm helping a company switch from a mongo database over to postgresql (yay!). The company is a marketplace app for musicians and hosts. The basic idea is that a host can book a musician for an event, like a wedding or a birthday. Also, an artist and a host can be either basic or "pro" accounts -- if they're "pro" then they pay a little bit more and get some extra features.

The design I'm struggling with is how to handle invoices and transactions in postgres. In mongo, everything is stuffed into a single 'invoices' table that includes sender and receiver addresses, the amount of the invoice, taxes, etc. It also contains a reference to the booked event, the artist and the host, as well as some state information through nullable columns -- created date, sent date, paid date.

At the same time the table also tracks the above mentioned "pro" subscriptions by utilizing a type field (so 'concertfee' vs 'subscription'). So both type of invoices are stuffed into the table and it's up to the application to understand the difference in the types.

To translate this to postgres, I'm leaning towards breaking out the different types of invoices into their own tables but keeping the basics of an invoice (sender, receiver, amount) and then referencing from specific tables like -- subscription_invoices and event_invoices.

so tables would be:
invoices (invoice_uuid primary key)

event_invoices (invoice_uuid FK, event_uuid FK)

artist_subscription_invoices (invoice_uuid FK, artist_uuid FK)

There is one last interesting part. When an event is booked, two invoices are generated -- one from the artist to the host for the payment of the concert, and then a second one from my company to the artist for the booking fee. Again, these seem like two separate tables, with, I suppose, a kind of a parent-child relationship (we can't have a booking fee unless we have the original invoice for the booking).

Thanks for reading --any insight, comments, or questions are appreciated!

Rob

Re: Invoice Table Design

From

Melvin Davidson

Date:

24 November 2016, 18:51:14

On Thu, Nov 24, 2016 at 9:17 AM, Robert Heinen <rob@216software.com> wrote:

I was wondering if anyone might be able to help me out with a table design question.

A quick intro -- I'm helping a company switch from a mongo database over to postgresql (yay!). The company is a marketplace app for musicians and hosts. The basic idea is that a host can book a musician for an event, like a wedding or a birthday. Also, an artist and a host can be either basic or "pro" accounts -- if they're "pro" then they pay a little bit more and get some extra features.

The design I'm struggling with is how to handle invoices and transactions in postgres. In mongo, everything is stuffed into a single 'invoices' table that includes sender and receiver addresses, the amount of the invoice, taxes, etc. It also contains a reference to the booked event, the artist and the host, as well as some state information through nullable columns -- created date, sent date, paid date.

At the same time the table also tracks the above mentioned "pro" subscriptions by utilizing a type field (so 'concertfee' vs 'subscription'). So both type of invoices are stuffed into the table and it's up to the application to understand the difference in the types.

To translate this to postgres, I'm leaning towards breaking out the different types of invoices into their own tables but keeping the basics of an invoice (sender, receiver, amount) and then referencing from specific tables like -- subscription_invoices and event_invoices.

so tables would be:
invoices (invoice_uuid primary key)
event_invoices (invoice_uuid FK, event_uuid FK)
artist_subscription_invoices (invoice_uuid FK, artist_uuid FK)

There is one last interesting part. When an event is booked, two invoices are generated -- one from the artist to the host for the payment of the concert, and then a second one from my company to the artist for the booking fee. Again, these seem like two separate tables, with, I suppose, a kind of a parent-child relationship (we can't have a booking fee unless we have the original invoice for the booking).

Thanks for reading --any insight, comments, or questions are appreciated!

Rob

Maybe it's just me, but I would go with a different design.
tables:

artist
    artist_uuid
    artist_type -- pro, basic
    artist_name
    artist...

event
    event_uuid
    event_type -- wedding, birthday, etc;
    event_...

invoice
    inv_uuid
    inv_type -- event, artist
    artist_uuid -- can be NULL (depends on inv_type)
    event_uuid -- can be NULL (depends on inv_type)

--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Invoice Table Design

From

Rich Shepard

Date:

24 November 2016, 19:11:10

On Thu, 24 Nov 2016, Robert Heinen wrote:

> A quick intro -- I'm helping a company switch from a mongo database over
> to postgresql (yay!). The company is a marketplace app for musicians and
> hosts. The basic idea is that a host can book a musician for an event,
> like a wedding or a birthday. Also, an artist and a host can be either
> basic or "pro" accounts -- if they're "pro" then they pay a little bit
> more and get some extra features.

Bob,

   I suggest you go back to first principles of database design. Start with a
list of all the information needed by the company, artists, audiences, etc.
Just list every item, regardless of type or to whom it belongs. Forget the
original database as it will only continue to confuse you.

   One you have listed every item of information, start normalizing by
collecting similar items into a group. As examples, account type (pro,
amateur); musicians (name, account_type, contact_information), hosts (name,
perferred_music_type, contact_information); musician_rates (many-to-many
table of musician name, account_types, and rates); etc. You can find good
references in books or on-line on how to normalize data. Look at Joe Celko's
books; they're very helpful.

   Only you and your client know just what's needed. This approach will open
your client to data/information they need that is not provided by their
current software and will provide you the basis for designing the postgres
schemas.

Rich

Re: Invoice Table Design

From

rob

Date:

29 November 2016, 11:45:30

Hi Rich,

thanks for the response -- going from Mongo to Postgres does require the
kind of approach you suggest.

I suppose my question was a little bit more along the lines if anyone has
experience with designing payment / invoicing systems and any caveats they
may have encountered along the way.

I have stumbled upon  table inheritance
<https://www.postgresql.org/docs/9.6/static/ddl-inherit.html>   available in
9.6, which I think might be helpful.

Thanks again for the feedback!

Best,
Rob



--
View this message in context: http://postgresql.nabble.com/Invoice-Table-Design-tp5931822p5932357.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Invoice Table Design

From

Berend Tober

Date:

29 November 2016, 14:11:43

rob wrote:
> Hi Rich,
>
> thanks for the response -- going from Mongo to Postgres does require the
> kind of approach you suggest.
>
> I suppose my question was a little bit more along the lines if anyone has
> experience with designing payment / invoicing systems and any caveats they
> may have encountered along the way.
>
> I have stumbled upon  table inheritance
> <https://www.postgresql.org/docs/9.6/static/ddl-inherit.html>   available in
> 9.6, which I think might be helpful.
>

The closest thing I have done to what you are describing is to create invoices that had multiple
component inputs, i.e., a section for time and labor, and a section for other direct expenses such
as materials and travel. This system employed a model something like the three-table outline you
suggested earlier where there is a top-level table that defines invoices and supplies a primary key,
and then the different types of component elements like the labor, materials, and expense  charges
each appear in different tables specific to the nature of those charges with a foreign key reference
to the invoice table. The invoice table primary key then provide a means to combine all the
accumulated charges together for final invoice presentation. I did not use table inheritance.

I looked at table inheritance years ago and ended-up running into some show-stopping problem related
to indexes. Review this thread for context:

https://www.postgresql.org/message-id/flat/60243.216.238.112.88.1102080278.squirrel%40216.238.112.88#60243.216.238.112.88.1102080278.squirrel@216.238.112.88

I have not followed whether table inheritance has changed, however, so there might be something new
others can help with. Me personally, I stick with the earlier sound advice of Melvin Davidson and
Rich Shepard and normalize.

Another caveat would relate to thinking about the response to invoices. Normally (or maybe naively),
one would expect to issue an invoice and then receive a timely payment. In reality, one may or may
not receive a timely payment. There might be end up being multiple invoices outstanding. You may
have to re-issue an invoice. And if you get a payment, it might be a partial payment, or a payment
that applies to more than one invoice. So you might want to think about a way to track and  apply
payments and partial payments to one or more invoices.

-- B

Re: Invoice Table Design

From

"btober@computer.org"

Date:

29 November 2016, 19:31:16


----- Original Message -----
> From: "rob" <rob@216software.com>
> To: pgsql-general@postgresql.org
> Sent: Tuesday, November 29, 2016 3:45:21 AM
> Subject: Re: [GENERAL] Invoice Table Design
>
> Hi Rich,
>
> thanks for the response -- going from Mongo to Postgres does require the
> kind of approach you suggest.
>
> I suppose my question was a little bit more along the lines if anyone has
> experience with designing payment / invoicing systems and any caveats they
> may have encountered along the way.


The other bit of experience I'll share is the suggestion that invoicing is a situation that lends itself to the
uniformlyincremented sequence pattern. Accountants and comptrollers love this. 

I detailed the pattern for a specific example of expense reports for which the business requirement was that expense
reportsbe identified by uniformly incremented integers on a per employee and per year basis, but the pattern applies to
manyapplication domains ... like invoice numbers. 


Implementation involves a control value to record the most-recently used key value and a before-insert trigger to
incrementthe value and effectively serialize inserts, allowing rollback that does not cause non-uniformities in the
sequence.


Some novice data base designers just like the idea of having no missing numbers in the sequence, and when they ask
aboutit on this forum, they usually get some well-deserved flak, but there is a very practical aspect from an auditing
perspective.Think of the situation with a traditional hard-copy check book. You count on the numbers being in sequence
toassure that no checks go missing or otherwise fail to be accounted for. A similar serialized accountability could
applyin many other circumstances. 

The whole message thread is here


https://www.postgresql.org/message-id/flat/758d5e7f0608171414l548db1e9x43f2372c560c3c2%40mail.gmail.com


Specifically my detailed explanation within that thread is here:


https://www.postgresql.org/message-id/44E376F6.7010802@seaworthysys.com


Some refer to this as the "gapless" sequence. Personally I dislike that term. Recently, I observed someone on this
forum(... sorry, I can't find a link to credit it ...) used the term "keyed sequence", which I find appealing enough to
considerit the proper term for this design pattern. 

-- B

Re: Invoice Table Design

From

Rich Shepard

Date:

29 November 2016, 20:02:54

On Tue, 29 Nov 2016, btober@computer.org wrote:

> The other bit of experience I'll share is the suggestion that invoicing is
> a situation that lends itself to the uniformly incremented sequence
> pattern. Accountants and comptrollers love this.

   Reading your message brought to mind a suggestion for Rob: look at the
source code for ledger-123 <http://www.ledger123.com/>. It's a fork of
SQL-Ledger which I've used for my business for almost 20 years. It has a
functional invoicing capability and should give you ideas on how to
structure your database and tables.

   I know you'll need customer, invoice, line-item tables at the minimum. But
since I use it only to generate service invoices and post them to accounts
payable I don't know the details of how it works. I do use postgres
(currently -9.6.1) and httpd with it.

HTH,

Rich