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:
- Find all
professionals
providingservice
id-s1 inlocation
id-l1. Sort by popularity/ratings of the professional. - Find all
locations
where aprofessional
id-p1 provides aservice
(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.
- Which Database system is better. A relational Mysql or a No-SQL MongoDb (and briefly why).
- 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