MySQL Schema – Inherent Shortcomings Analysis

MySQLschema

Events Registration Schema Diagram

This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the parent anyway (perhaps I'm mistaken!) sqldbm.com schema diagram

  1. Is there significant value in pulling in every foreign key from the parent and making them part of the child's primary key? Is there a way to automate setting these additional keys? (I.e. a ticket row being a child of an event row which is a child of a location row and the ticket row also includes location_id and needing to make sure that gets set correctly.)

  2. Does anyone see anything in the upper schema diagram that might shoot me in the foot down the road? Not pictured but desired is an additional many-to-many mapping to event questionnaire responses also attached to a ticket (i.e. a registrant providing answers to experience levels in a sport discipline, gear questions, etc as part of their ticket purchase). I am already planning on removing the multiple locations for costs and having option value be the only place cost is found and always having an option value supplied for each event.

Best Answer

Unfortunately, it's very difficult to just cast one's eye over a schema and say, "Yep that's great" or "Yes, that's going to work".

Schema and system design is an iterative process - you start with a few ideas, implement them and test to see how it's working. Then you rejig the bits that aren't working and see - now the rejigging will (in all likelihood) break some bits that were working previously and that you hadn't changed - so you tweak again, and hopefully you asymptotically approach (nirvana...) a decent working app.

I can only advise you to

  • visit databaseanswers.org - check out databaseanswers - there's lots of links related to tickets and events, and

  • check out your search engine of choice for "open source reservation systems" (or similar) and download some systems and check out their schemas.

Sorry not to be able to be of more help, but it's analagous to asking "I'm thinking about writing a book, here are the chapter titles, will it be good?".

Start out your system and if you have more specific questions, come back here and ask them - p.s. welcome to the forum.

Related Question