I am trying to create a database design, which is quite similar to classpass.com. However I am new to database design and not sure if I am on the right way.
The idea is: I have multiple cities and each have multiple neighborhoods. In each neighborhood, you have multiple studios/venues who offer different kind of trainings and classes. (Example could be seen here: https://classpass.com/classes, https://classpass.com/studios)
So what I got so far is something like:
So what I am not quite sure about is:
1. I am not sure how to store the venue/studio address and geolocation. Is it better to have table Region which defines id | name | parent_id and stores the cities and the neighborhoods recursively? Or add a foreign key constraint to city and neighborhoods? Should I store the lan/lon into the venue table, into the address or even separate locations table? I would like to be able to perform searches like:
- show me venues in that neighborhood or city
- show me venues which are in radius XX from position
2. Each class should have a schedule and currently I am not sure how to design it. For example: Spinning class, Mo, We, Fr from 9 AM till 10 AM. I would like to be able to do queries like:
- show me venues, which have spinning classes on Mo
- or show me all classes in category Spinning, Boxing for example
- or even show me venues offering spinning classes
3. Should I create an extra table schedules here? Or just create some kind of view which creates the schedule? If it's an extra table, how should I describe start, end of each day of the week?