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? (IE 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 questionaire responses also attached to a ticket (IE 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.

  • 1
    Can you elaborate on 1) "Is there significant value in pulling in every foreign key from the parent and making them part of the child's primary key?" ? Pick two tables and ask the question why should/shouldn't this foreign key relationship exist. – Lennart May 11 '18 at 3:43
  • See the difference between the top diagram and the bottom. the sqldbm design tool wanted to pull in every key from the parent and I had to manually edit them out. I'm wondering if there's a reason the tool was written to have that default behavior, is there any value in pulling in all keys, other than being able to skip intermediate joins and queries to walk up the tree of relations? – UnlikelyNomad May 11 '18 at 14:11
  • I guessed that the blue PK-FK lines were indicating an FK to some other table, not that this was part of the current PK. – Rick James May 24 '18 at 17:46

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.

  • Thanks. Wasn't expecting some answer as to the longevity, mostly technical correctness. My first time really working to relate tables and in particular my first time with many-to-many. Definitely checking out that link. – UnlikelyNomad May 10 '18 at 19:22
  • A first look seems to suggest to me that it looks OK. Basically, it's a bit like this - and anyone who says that 95% of software development doesn't work like that is either an academic or works for Microsoft :-) – Vérace May 10 '18 at 19:51
  • It's funny. I'm a lot more loose with development iterations for actual work than I am personal projects. But I have a lot more experience with control systems, I/O, and automation than with database design. – UnlikelyNomad May 10 '18 at 20:37
  • Well, best of luck with the project - can't really add any more - just hope you found my answer even a little helpful. – Vérace May 10 '18 at 20:53
  • Accepting for that awesome link to databaseanswers' models – UnlikelyNomad May 11 '18 at 3:47

A few specifics:

  • Don't use DOUBLE for money; use DECIMAL(m,n)
  • Consider using shorter versions of INT and consider UNSIGNED.
  • The diagrams don't show whether you have composite indexes or single-column indexes.
  • Don't expect to finalize a schema before having the SELECTs at least sketched out.
  • I see a couple of CHAR columns -- are they yes/no flags?
  • Good: I'm glad you did not normalize city and state.
  • For many:many performance tips, see http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.