Обсуждение: Invoice Table Design

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

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.

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

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

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