Which is a more efficient way of storing data in a database using Objects or strings (Database design)

Question

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}

Answer 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.

Answer 2

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.

Answer 3

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.