I constructed this little ERD for Project Butterfly in LucidChart in about 20 minutes. I use standard entities and crow's foot notation to mark the one-to-many relationships. This should generally do it for the first iteration of a simple convention tracking system.
This is stupid simple -- as schemas can get enormous over time. Some basic thoughts:
- The User record table will become plump with columns over time.
- Convention table will become plump with columns over time, too.
- 1 convention can have multiple pass types, and a pass type can have a duration....
- But 1 customer has 1 pass type with 1 convention.
- Choose fixed width CHARs over VARCHARs as it is easier on disk and RAM. In constructing a table, go with all CHARs or VARCHARs and do not mix.
- DATETIMEs are stored as LONGs in memory and on disk.
- Queries are heavily INTEGER focused.
I haven't written the INDEXes yet.
Also, I am not terrifically crazy about maintaining that USED counter. Multiple services or multiple workers all trying to update USED at the same time will run into row lock contention and this will turn into a scalability burr. Better to put a value like that in a cache and occasionally update the database.
Memcached has an atomic increment (it will add and commit in one action) unique to memcached that is perfect for counters like these. We would want to cache MAX, too, since once the campaign starts it doesn't change. But caching is not in our original list of requirements for the first iteration, so it goes into backlog. This blog is agile!
Things for the Backlog:
- Creating a nice flow for the website.
- Adding prices to options.
- Creating reports.
- Creating accounts.
- Interfacing with local lodging options.
- Add Indexes to the schema
- Add a Caching System for Maintaining Sold Pass Counts