Data structure from MySql to FireBase (NOSQL db)

Question

I have read plenty of tutorials and stackoverflow Q&A and I got to the point of trying to design a database structure for Firebase. I tried to keep it FLAT (it seems to be a pretty important consideration). At the same time I don't know all the possible query that I will apply to the FireBase db (at the end of this post I gave some basic examples). So I'm looking for the help of someone who has already worked with Firebase and come across the main issues of the people coming from the Relational database

Giving the following Mysql structure

    TB_USER
    +----+--------+-------------+---------------+-----------------------------+------------------------+------------+
    | id |  name  |     bio     |     link      |           avatar            |         userId         | created_at |
    +----+--------+-------------+---------------+-----------------------------+------------------------+------------+
    |  1 | Fabian | bla...bla.. | www.site.com  | img_on_the_Server.jpg       | StringFromAuthFBGoogle | 20-02-2018 |
    |  2 | Sarah  | bla...bla.. | www.sarah.com | img_on_the_Server_Sarah.jpg | StringFromAuthFBGoogle | 18-02-2018 |
    |  3 | Carl   | bla...bla.. | www.carl.com  | img_on_the_Server_Carl.jpg  | StringFromAuthFBGoogle | 14-02-2018 |
    +----+--------+-------------+---------------+-----------------------------+------------------------+------------+


    TB_JOURNEYS
    +----+----------------------+----------------------+--------+------------+
    | id |     journey_name     |     description      | iduser | created_at |
    +----+----------------------+----------------------+--------+------------+
    | 28 | Traveling to India   | desc of India        |      2 | 21-02-2018 |
    | 34 | A week in China      | desc of China        |      1 | 21-02-2018 |
    | 46 | South America by car | incredible adventure |      3 | 22-02-2018 |
    +----+----------------------+----------------------+--------+------------+


    TB_STAGES
    +----+------------+------------+------------+--------------+------------------------------------------------+-----------------------+-----------------------+
    | id | id_journey |    date    |  latitude  |  longitude   |                      text                      |        picture        |         video         |
    +----+------------+------------+------------+--------------+------------------------------------------------+-----------------------+-----------------------+
    | 10 |         28 | 20-12-2017 | 46.3991665 | -117.0484223 | Fantastic day                                  | image_of_that_day.jpg | video_of_that_day.mp4 |
    | 20 |         28 | 23-12-2017 | 14.6082829 | -90.5528772  | Another beautiful day walking through the city | img_art.jpg           |                       |
    | 30 |         46 | 01-01-2018 | 45.462889  | 9.0376466    | Center of the city                             | pic.jpg               | video.mp4             |
    |    |            |            |            |              |                                                
    |                       |                       |
    +----+------------+------------+------------+--------------+------------------------------------------------+-----------------------+-----------------------+

I came up with this FireBase structure

{
  "users": {
    "1": {
      "name": "Fabian",
      "bio": "bla...bla",
      "link": "www.site.com",
      "avatar": "img_on_the_Server.jpg",
      "userID": "StringFromAuthFBGoogle",
      "created_at": "20-02-2018"
    },
    "2": {
      "name": "Sarah",
      "bio": "bla...bla",
      "link": "www.sarah.com",
      "avatar": "img_on_the_Server_Sarah.jpg",
      "userID": "StringFromAuthFBGoogle",
      "created_at": "18-02-2018"
    },
    "3": {
      "name": "Carl",
      "bio": "bla...bla",
      "link": "www.carl.com",
      "avatar": "img_on_the_Server_Carl.jpg",
      "userID": "StringFromAuthFBGoogle",
      "created_at": "14-02-2018"
    }
  },
  "journeys": {
    "28":{
      "journey_name": "Traveling to India",
      "description": "desc of India ",
      "created_at": "21-02-2018",
      "iduser": 2
    },
    "34": {
      "journey_name": "A week in China ",
      "description": "desc of China ",
      "created_at": "21-02-2018",
      "iduser": 1
    },
    "46":{
      "journey_name": "South America by car",
      "description": "incredible adventure",
      "created_at": "22-02-2018",
      "iduser": 3
    }
  }, 
  "stages": {
    "10": {
      "id_journey": 28,
      "date": "20-12-2017",
      "latitude": "46.3991665",
      "longitude": "-117.0484223",
      "text": "Fantastic day ",
      "picture": "image_of_that_day.jpg",
      "video": "video_of_that_day.mp4"
    },
    "20": {
      "id_journey": 28,
      "date": "23-12-2017",
      "latitude": "14.6082829",
      "longitude": "-90.5528772",
      "text": "Another beautiful day walking through the city",
      "picture": "img_art.jpg"
    },
    "30": {
      "id_journey": 46,
      "date": "01-01-2018",
      "latitude": "45.462889",
      "longitude": "9.0376466",
      "text": "Center of the city",
      "picture": "pic.jpg",
      "video": "video.mp4"
    }
  }

}

The real problem is that I have never worked with a NOSQL Db so I don't know if the structure can answer the basic questions that we all have to answer while we are using a Relational DB. In the app I would have to retrieve all the journeys that belong to a specific User and for sure I will have to retrieve all the Stages that belong to a specific journey. I will search for a specific user (searching by name)

Answer 1

Of course, NoSQL is not easy for programmers who worked for years with relational databases. But as in SQL at self, you can do a lot of programming in the app at self.

Here's an example: You want to find the text with 'Center of the city'.

SQL

SELECT * FROM TB_STAGES WHERE text='Center of the city'

NoSQL

You have to iterate through each object which is included in the attribute "journey" and check if the text is equal to the text which are you searching for.

Here is a pseudocode, how this can be implemented:

for(i = 0; i < journey.length; i++){
   curr = journey.get(i);
   if(curr.text == search_text){
      //Do what you want
   }
}

I hope this might be helpful for you to understand, that you can always make workarounds on the app/client application at self. It is not always the best solution but it works.

Answer 2

It seems you're on the right track:

• You realize that this is not the same as a relational database.
• You've learned to keep your data structure flat.

Some lessons based on what you've shared though:

• You're using numeric (and frequently sequential) values as the key. For best scalability it's preferred to use more intrinsically unique values for the keys, such as:

  • If you use Firebase Authentication, each user has a UID that is already unique. Since each user can only us under /users once, storing them as /users/$uid means that you automatically enforce this uniqueness.
  • If the items don't have a unique ID yet, consider using Firebase's push IDs. While they're not as convenient to read for humans as the "array indices" you're using, they have advantages around scalability and offline behavior that make them much better suited in Firebase.
• For many-to-many relations you'll probably want to create "index nodes" in both directions: e.g. from users to journeys that they're in, and from journey to the users that are on it. For more on this, see my answer here: Many to Many relationship in Firebase
• In general you'll end up modeling your data for the use-cases of your app. Since you don't know all of those yet (that's normal), you can't determine a final data model yet. That's fine too. You'll expand/change your data model as you uncover more use-cases.
• Model data as it shows on the screens of your app. E.g. if you have a screen with a list of user names, consider having that list of user names in your database. Sure, you can also load the user profiles and display the names, but if you only have the names, you'll waste less bandwidth.
• I can't recommend enough that you read NoSQL data modeling and watch Firebase for SQL developers, which are great introductions/primers/eye-openers to the topic.