Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    1

    Database design help - storing geolocations

    Hello,

    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:

    Click image for larger version. 

Name:	design.png 
Views:	4 
Size:	395.2 KB 
ID:	16922


    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?

    Thanks for the help.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495

    classes

    I use these tables ,
    tClasses
    [classID], [ClassName], [meetings], [StartDate],[EndDate], venue, Instructor
    242,Spinning class, "Mo, We, Fr from 9 AM till 10 AM", 1/1/16, 3/1/16....

    then vba code builds a list (based on items above) of every date the class meets Click image for larger version. 

Name:	new class.png 
Views:	0 
Size:	48.2 KB 
ID:	16927

    tClassDates
    [DateID] [classID],[ClassDate], [Status]
    1, 242, 1/1/16, "meet"
    2,242, 1/3/16, "canceled"

    (this is where youd query Mondays , Wednesdays

    tAttend
    [DateID], [StudentID], [StudentStatus]
    1, 23 , "attend"
    1, 44, "absent"

  3. #3
    Join Date
    May 2016
    Posts
    89
    Hi

    Hi 089dimi

    For modeling correctly a db,


    1. Apply at least the 3 first Normal forms (NF)
    2. Use a dedicated tool like
      • Power*AMC (excellent but expensive)
      • JMERISE, excellent freeware here

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •