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

data typesdatabasedatabase-designjsonsql

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}

Best Answer

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.

Related Topic