When dealing with databases, does adding a different table when we can use a simple hash a good thing?

Question

For example, here's the problem I faced... I have three tables. Products, Districtrates, Deliverycharges. In my app, a product's delivery will be calculated through a pre-defined rate defined in the Districtrates table. If we want, we can also add a custom rate overriding the pre-defined rate. Each product can have all 25 districts or only some. So here's my solution :

Create three tables as I mentioned above. Districtrates table will only have 25 records for all the 25 districts in my country. For each product, I will add 25 records to the Deliverycharges table with the productID, deliveryrateID and a custom rate value if available. Some products might have less than 25 districts (Only the ones available for that product).

I can even store this in a simple hash in one cell in the products table. Like this : {district1: nil, district2: 234, district4: 543} (It's in Ruby syntax). In here, if the value is nil, we can take the default value from the deliveryrate table. Here also the hash will have all 25 districts! But the above method (creating a table) is easy to work with. The only problem is, it will add nearly 25 records per each product.

So my question is, is this a good thing? This is only one scenario... There are more where we can use one simple array or hash in a cell rather than creating a table. Creating a table is easy to maintain. But is it the right way?

Answer 1

One of the main points of using a relational database is the ability to query (and update) the data in it using SQL.

That only works if you put the data in a form that the database actually understands. Traditionally, this means defining a table schema.

There are now extensions to let the database work with "semi-structured" data (such as XML/JSON/JSONB), but you should only need to go there when the data really does not fit into the relational model, otherwise you are giving up on a lot of features/performance.

If you put a Ruby string into a text column, you will not have any way to use it from SQL. So no proper searching, indexing, or efficient updates of these delivery rates.