Re: Invoice Table Design

On Thu, Nov 24, 2016 at 9:17 AM, 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
>

*Maybe it's just me, but I would go with a different design. tables:*

* artist artist_uuid artist_type -- pro, basic artist_name
artist...event event_uuid event_type -- wedding, birthday, etc;
event_... invoice inv_uuid inv_type -- event, artist
artist_uuid -- can be NULL (depends on inv_type) event_uuid -- can be
NULL (depends on inv_type)*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.