Store And Retrieve data to Mysql DB which has many to many relations without creating a new third table and keeping intact the normalization

Store and retrieve data which has ‘many-to-many’ relationships with each other without creating a third table

This article is intended for website and software developers who have some knowledge about programming and Databases.

During development in any computer programming language, we usually come across situations where we have to store data taken from different Media like web forms, CSV files, RSS Feeds etc.

There are also different ways to store the data, like storing data to an XML file, storing data to a CSV file and/or storing data to any database.

Many times software and website developers face a situation in which data has ‘many to many’ relationships with each other. And for this purpose, they make an extra SQL data table in any Database which they use. This is done just to keep the database normalized.

To make life easy, let’s take a simple example and see something about normalization step before moving to the actual topic of this article. I am explaining it here just for the developers who have less knowledge about database and development and are at beginning level and also for making the ground for my solution.

Suppose you are working on a project which is related to any restaurant website. A restaurant website mostly has Menus, Recipes/dishes etc. If you explore further, you will see that the Menus can have many recipes/dishes whereas a dish can be served in different menus; Here Menus are Breakfast, Lunch, Dinner, and Supper etc. Dishes can be Bread, Juices etc.

So you can serve bread in Breakfast, You can serve in Dinner etc. And a menu can have many types of dishes, for instance, bread, Fried chicken, Berger etc.

In the above example, you can see that there is a ‘many to many’ relationships between dishes and menus. In order to store data and information for dishes and menus, you will definitely make a data table for Menus, a data table for Dishes. Now if you try and save the data, you will realize that there is a repetition of fields in Dishes table.

The Problem

Data is not normalized.

See the screenshots below for two different tables tblmenu and tbldishes created in MYSQL Database. Please note that these tables are just for explanatory purposes and do not contain all the necessary data fields.

tblmenu

tbldishes

Suppose there are Two Menu items in tblmenu and some dishes in tbldishes tables respectively.

Screenshots of both tables with values are given below:

You can see that ‘Bread’ is served not only in ‘Breakfast' but also for ‘Dinner' and it is being repeated. Here is the solution:

The solution

The solution to this problem is to create a new third intermediate table as most of the developers do.

I am not going to create any new third intermediate table. Instead, I am moving towards another solution which will suggest how to solve the above problem without creating a new table and keeping intact the normalization.

New solution

Creating a new table will solve the issue of normalization without any problem. But when I used to do that, I always had to know which the third table was related to which other two tables in a large database. Also while coding, I would have to store data to that third table and then at query time, I had to make such a query which could fetch the required data from the second table or first table depending upon the third table. It was extra overhead and work which I had to do.

Here is an idea on how this problem can be resolved with just a simple SQL query.

The Idea

I will move forward by again taking the example of a restaurant which we are dealing with at the moment. Suppose you have a web page where you have to display some dishes, and you have selected a dish to say Bread and you have added it to or attached it with a (or many) Menu like Breakfast or/and Dinner.

Now you need to store that to the DB so that it might be available to the user not only for Breakfast but also for dinner.

Do Not go and create a new table in order to store the selected Dish and Menu’s id.

To do so, just make a simple change in tbldishes by changing the type of Menu id foreign key field to Varchar instead of int.

Now you just need to store or insert the id(s) of the selected menu separated by Commas into that field. e.g, 1, 3

It means Menu # 1 and Menu # 3 have been selected.

Using this technique, there is no need of making the third table or calculating a Binary value of the Ids and then summing the Ids up in order to store to the Db menu foreign key Field. You can see the data in the table is normalized.

Also, there is no need to count Total # of Menus in order to calculate 2 ^ n-1 formula for making # of bits and then doing the bit shifting operations on MySQL side in the query.

The total cost which will come on this action that is concatenating the Menu ids is just constant and minimum, which makes this solution fast and efficient.

Ok, this was the answer of how to store the data.

Now the concern is how to retrieve a Recipe/Dish which is present in different Menus.

That is so simple, easy and efficient using the method I am going to tell you now. No need to be worried about shifting a correct number of bits and performing bitwise operations etc or making complex SQL queries for retrieving data.

IF YOU WANT TO CHECK ALL THE RECIPES/DISHES WHICH ARE AVAILABLE IN BREAKFAST AND HAD ID EQUAL TO ONE (1) IN DB TABLE, JUST DO THIS

SELECT dish_Name FROM tbldishes WHERE dish_MenuIdFk REGEXP '1'

OR

SELECT dish_Name FROM tbldishes WHERE dish_MenuIdFk REGEXP '[1]'

Both will work. You can confirm it by using an example to see the result yourself. No need to manipulate bits or making complex queries.

Again, if you want all the recipes in menu 1 or 4, do the following:

SELECT dish_Name FROM tbldishes WHERE dish_MenuIdFk REGEXP '[1 | 4]'

And if you want 1 and 4, do like this.

SELECT dish_Name FROM tbldishes WHERE dish_MenuIdFk REGEXP '[1]' AND dish_MenuIdFk REGEXP '[4]'

So will it work for digits (ids) greater than 9?

No, it will cause some repetition.

So what is the solution?

Let's make some modification to improve it. Store the values from your programming code separated by ‘!' or any other character which has no Special meaning in REGEXP. ‘!' this will work as a boundary of the number or Menu id.

I am using '!', so store the values like this

!1!!333!!4! So on

And retrieve as

SELECT * FROM tbldishes WHERE dish_MenuIdFk REGEXP '!1!'OR dish_MenuIdFk REGEXP '!33!' LIMIT 0, 30

It will work up to any number of digits within a number.

Conclusion:

In my example, only the operation of the concatenation of the Ids is being performed before storing them to the DB. In the routine way in which we make a third table, you need to use some loop in order to store multiple ids within the table. This will definitely take much time than just performing a simple concatenation operation.

One can also enhance and optimize the query by indexing the columns which one think will be used for search filters. In my example, you can put the “Index” on “dish_MenuIdFk”.

I would appreciate the addition or subtraction and any comment on it in order to make it better.

Note: The command which has been used here in the solution within SQL query is ‘REGEXP'. This is an MYSQL command. You will have to see the alternative command in your database manual if you are not using the MYSQL database.

Thanks

Syed Murtaza Hussain Kazmi
Senior Project Manager, Senior Program Architect
Efficiency in code is good but that must be reasonable.