Re: Invoices Table Design Question

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

What you describe seems to be a material for table inheritance in
PostgreSQL (/docs/9.6/static/tutorial-inheritance.html)
So, you may want to consider it. But also review the caveats section
at (/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

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 (/docs/9.6/static/tutorial-
> inheritance.html).
> So, you may want to consider it. But also review the caveats section
> at (/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
>

On Tue, Nov 29, 2016 at 2:06 PM, Robert Heinen <rob(at)216software(dot)com> wrote:
> 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.

Inheritance is supported for quite long, from 7.2 per documentation.
So, it's available in all the supported version right now. You do not
need to upgrade to 9.6 just for that. But yes, 9.6 has loads of
features, which might be of interest.

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