Re: Invoices Table Design Question

What you describe seems to be a material for table inheritance in
PostgreSQL (https://www.postgresql.org/docs/9.6/static/tutorial-inheritance.html).
So, you may want to consider it. But also review the caveats section
at (https://www.postgresql.org/docs/9.6/static/ddl-inherit.html).
Instead you might want to create a single table with a field
indicating which of optional fields are useful OR store common
information in a single table for all invoices and then create
separate tables for different types of invoices, for specific
information and link those to common table using primary key and fkey
constraints.

On Thu, Nov 24, 2016 at 7:38 PM, Robert Heinen <rob(at)216software(dot)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

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company