Invoice Table Design

From: Robert Heinen <rob(at)216software(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Invoice Table Design
Date: 2016-11-24 14:17:16
Message-ID: CAKQp+OkdU+4961rquUONZn+Z3T+M68p9RczCOD3421My4SC9_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vick Khera 2016-11-24 14:37:31 Re: Backup "Best Practices"
Previous Message 王波 2016-11-24 07:52:34 PostgreDB stores table name is lower case