Model/Schema Design

[williamratcliff]

I suggest the following tables for the database (ids/foreign keys implied):

Organization address url latitude (do we put this with address, or leave it as part of the organization) longitude name services hour open (datetime) hour closed (datetime) phone number (can be null)

Address street zipcode city (included for extensibility) state (included for extensibility--also if VA or MD)

Service (populate on creation) name

Category (populate it on creation)

CategoryService (so we can map categories to services)

[williamratcliff]

I have a separate table for services so that we can services in the future without migrating the database.

[Serubin]

I've normalized the database - specifically to ensure fast geolocation queries. I've also assumed that the database doesn't care what services are in which categories, as this should be hard coded on the front end.

Organization (Primary table with primary information) Primary info is information that will be most likely to be queried against.

• id
• service_id
• category_id
• hour open (datetime)
• hour closed (datetime)

OrganizationInfo (Information table) Contains data that only needs to be displayed, and not queried against.

• o_id (organization id)
• name
• url
• phone number (can be null)
• street
• zipcode
• city (included for extensibility)
• state (included for extensibility--also if VA or MD)

Location Separate table for long & lat for quick

• o_id
• latitude
• longitude

Service (populate on creation) Contains all possible services

• id
• name

Category (populate it on creation) Contains all possible Categories

• id
• name

[williamratcliff]

I have now added this, but we should think very seriously about how we want to handle migrations given: https://www.algotech.solutions/blog/python/django-migrations-and-how-to-manage-conflicts/