1

Which method is better to store the data in a database and why? Here is a restaurant example. I'm using a SQL database and JSON to communicate with the database.

Imagine each row is a column in a database

  1. Mostly without objects

    • Resturant ID -string
    • Resturant name - string
    • Resturant picture - hyperlink
    • Resturant description - string
    • Resturant hours - String
    • Resturant is open - boolan
    • Resturant type - string
    • Location - string with lat and long
    • Resturant Menu- array of objects {item name, price, description, type, picture}
    • Resturant rating - array of objects {rating, description, critic_id}
  2. Mostly with objects

    • Resturant ID -string
    • Resturant info - object {name,picture,description, hours, isOpen, type, location}
    • Resturant Menu- array of objects {item name, price, description, type, picture}
    • Resturant rating - array of objects {rating, description, critic_id}
  • 6
    Hoe do you personally define "better?" Do you want storage space economy? Ease of querying? Most likely to succeed in a tractor pull? – Robert Harvey Nov 14 '17 at 1:51
  • 3
    For a SQL database, you're almost certainly going to want to favor individual fields, as in example 1. Your arrays of objects are most likely going to be other tables, not arrays of objects. – Robert Harvey Nov 14 '17 at 1:55
  • Idk how to define better. I guess maybe I should have reworded my question of pros and cons of both designs. I had played with Mongol before but if I was to use anything to build a website or app I'd use google firebase. However, since firebase has a weird design when you put data in it I've been transferring data from my firebase to spreadsheet to give it the table feel. – Sebastian Nov 14 '17 at 1:58
  • 2
    The main trade off is relational database tables are very low-level data structures, requiring explicit keys to join things together, and only providing two data structures, tables and collections of tables. Objects can be higher level in abstraction, but as far as serialization is concerned, that depends on the tools you use (serialization being saving and retrieving the objects from persistent storage). – Frank Hileman Nov 14 '17 at 2:03
1

In an relational SQL database, there are no objects, and you have to map your data so that in the end everything is in a field of one of the fundamental db types (int, strings...) in a table.

One remark about your first scheme: there are no arrays in sql either. You have to convert the arrays into a table with its own rows, adding a foreign key that allow to connect the array items to the restaurant ID.

In order to keep the benefits of objects, you should isolate the database mapping in a data source layer that makes the mapping between both worlds. So if later you change the db component you do not put in question all your code. For instance If at some moment in time you'd switch to a no sql document database, such as MongoDB, the object approach would be a better fit.

1

Although performance and ease of replication can be areas of concern, when you're trying to decide between sql and nosql, consider the structure or lack of it in your data.

You've proposed a fairly simple data structure for tracking eating establishments, so I don't think it maters. However, what happens when you need to get a little more complicated because fast-food restaurants require information specific to them and high-end 5 star restaurants have a completely different set of data requirements. Trying to manage them both in the same data structures can get complicated. NoSQL databases tend to handle this type of thing a little better. Data are typically structured in the way you want to query them - boom, it runs very fast.

SQL databases tend to run better when data can fit nicely into columns (no variability or surprises) that usually get filled with data. Being able to related to data in other tables offers consistency and allows for unforeseen and complex ways of querying the data. Indexing can try and offer the best of both worlds, but you better know what you're doing and consider the performance hits on making changes. Maybe making changes isn't a big deal. After all, restaurants don't change every 3 seconds.

Unless this a learning situation, go with the one you know best. You'll be able to modify it in a timely-fashion and tweak as needed. Decide on some metrics early on and monitor them so you have an idea of what indicators may pop-up that suggest you're using the wrong platform.

0

Do both. In doxdb I used that approach. The concept is to store data that would be queried and indexes separately from the actual data. In doing so you can be a little more liberal with the schema design.

You can use mongo or elastic search to do this as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.