Seeking feedback on table relationships, general schema design I'm designing a database for a business that sells tickets to a variety of events worldwide. The app will be built with Rails using PostgreSQL.

Here's what I have so far, link to full size image



I'm mostly concerned with the relationship between EVENT_CATEGORIES / EVENT_SUB_CATEGORIES / TEAMS_ARTISTS / COMPETITIONS.

A typical path for a user to take on the app would be

Sports (event_category)  ---> Football (event_sub_category)  ---> Champions league (competitions)  ---> Arsenal (teams_artists), skipping competitions for events without competitions e.g, Concerts.

Will these relationships hold up in a production environment? Is there anything more optimal I'm overlooking? Any other tips in general would be welcome and appreciated.

Thanks!
Event Category and Sub-Category are classifications of events. They are your way of tagging events (i.e. meta-attributes).  The Event itself is a physical-thing/occurence (independent of you)  to which you are attaching these attributes.  Competition and competitors are also physical things- that are associated with the occurrence. When designing, it helps to be aware of what is a physical thing, and what is your design artifact.

I don't think Competition , or Competitors or Performers (if a non-sport event) should depend upon your classification.  

** So I would take out the link between sub-category and competition. **  

An Event has performers.  A performer can be an individual or a team.
An event will be associated with one or more performers. 

A competition like UEFA-CL can be considered a collection of events (*Not the otherway around - as in your model *). Hence competition is independent (or rather, a higher level entity) compared to an event. An Event is kind of like the 'delivery' (or instance) of that competition at a particular time / place.

Hence, Competition --> has multiple (> 0) events --> has multiple (> 0) performers. (This is the way I would look at competitions).  So I would make competition above the Event (in the object heirarchy - not talking of inheritence here)

I can talk about UEFA-CL without referring to a specific event in its set. Competition is composed of (stable) teams.  The competition can span seasons - so you might even have search/drill-down criteria such as Soccer.  You need to setup your production so that you can do analysis easily on your data too. I wouldn't want to bury competition deep inside your heirarchy.