Re: Invoices Table Design Question

Hi Ashutosh,

thanks for the response.

I think this seems like a good enough reason to upgrade to 9.6 and try out
inheritance. It seems to be more or less what I'm looking for.

Best,
Rob

On Thu, Nov 24, 2016 at 11:46 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:

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