mercredi 5 août 2015

Database Schema design review (mysql or mongodb)


I am designing DB schema for a project. I have 3 entities:

  • professionals
  • services
  • locations

One professional could provide multiple services (~5) at multiple locations (~1000). One location could have thousands of professionals providing same service and vice-versa.

The queries will be of the form:

  1. Find all professionals providing service id-s1 in location id-l1. Sort by popularity/ratings of the professional.
  2. Find all locations where a professionalid-p1 provides a service (id-s2).

Caveats - locations also have an associated lat/lng, apart from a standard id. If there are no direct matches for a service in location l1, we may need to search the professionals within radius r from the lat/lng taken given location.

I need to understand two things.

  1. Which Database system is better. A relational Mysql or a No-SQL MongoDb (and briefly why).
  2. Validation of the schema given below (and suggestions for improvements).

Mysql

professionals | id | name
-----------------------------
                1  | alex
                2  | bob
                3  | charles

services      | id | name
-----------------------------
                1  | stenography
                2  | underwriting
                3  | insurance

locations     | id | lat       | lng         | name
--------------------------------------------------------
                1  | 38.362031 | -98.477500   | office1
                2  | 39.362031 | -99.477500   | office2
                3  | 40.362031 | -100.477500  | office3
                4  | 41.362031 | -101.477500  | office4
                5  | 42.362031 | -102.477500  | office5


services-prof | id | prof_fk | services_fk
--------------------------------------------
                1  | 1       | 1
                2  | 1       | 2
                3  | 1       | 3
                1  | 2       | 1
                2  | 2       | 7
                3  | 3       | 1

location-serv-prof | id | prof_fk | services_fk | location_fk
---------------------------------------------------------------
                      1 | 1       | 1           | 1
                      2 | 2       | 1           | 1
                      3 | 3       | 1           | 1
                      4 | 4       | 1           | 1
                      5 | 5       | 1           | 1

MongoDb

{
    "_id": "t356ah7q",
    "first_name": "Alex",
    "last_name": "Johnson",
    "addresses": {
        "primary_address": {
            "_id": "5765675",
            "lat": "38.362031",
            "lng": "-98.477500"
        },
        "other_addresses": [
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            },
            {
                "_id": "5765675",
                "lat": "38.362031",
                "lng": "-98.477500"
            }
        ]
    },
    "services": [
        {
            "service_id": "stenographer",
            "locations": [
                "loc1",
                "loc2",
                "loc3"
            ]
        },
        {
            "service_id": "underwriting",
            "locations": [
                "loc5",
                "loc6",
                "loc7"
            ]
        }
    ]
}



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire