On Thu, Nov 24, 2016 at 9:17 AM, Robert Heinen <r...@216software.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.
