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