Invoice Table Design

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt; 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt; 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:

/messages/by-id/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- 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 uniformly incremented 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 reports be identified by uniformly incremented integers on a per employee and per year basis, but the pattern applies to many application domains ... like invoice numbers.

Implementation involves a control value to record the most-recently used key value and a before-insert trigger to increment the 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 about it 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 to assure that no checks go missing or otherwise fail to be accounted for. A similar serialized accountability could apply in many other circumstances.

The whole message thread is here

/messages/by-id/758d5e7f0608171414l548db1e9x43f2372c560c3c2@mail.gmail.com

Specifically my detailed explanation within that thread is here:

/messages/by-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 consider it the proper term for this design pattern.

-- B

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/&gt;. 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general