CODEWITHSUNDEEP
mysqldatabase-designdatabasedatabase-normalization
Prashant Singh
Prashant Singh

Reputation: 3793

Efficient read and write in mysql database

I have a mysql table which stores a set of queries (strings).

The table with its contents looks like this :-

query_id   queryString
  1.         Query 1
  2.         Query 2
  3.         Query 3

The results table which are somehow related to the above mentioned queries are stored in a different mysql table in the form shown

result_id   query_id  resultString
   1.           1      Result 1
   2.           1      Result 2
   3.           2      Result 3
   4.           2      Result 4
   5.           2      Result 1
   6.           3      Result 3
   7.           3      Result 4
   8.           3      Result 5

Clearly, the model above has redundancy, as I have to store Result 1 , Result 3 and Result 4 more than once. This redundancy further increases with increase in number of similar queries. So, lets say if I have to do some processing on the results query, I would have to do on several duplicate values.

In another alternative which I can think of is that I can store results uniquely in a table and store the results_id to which they refer along with the queries in the query table. But in that case, while reading results for a query, I would have to hit a number of mysql queries, one corresponding to every result_id that I have. So, that seems inefficient(w.r.t read) to me.

What other possible solutions could help me in removing redundancies with minimal increase in the read/write load ?

Please comment if I am unclear in asking my doubt.

Thanks !

Upvotes: 0

Views: 81

Answers (1)

Jonysuise
Jonysuise

Reputation: 1830

It seems this is a N:N relationship between querys and resultstrings so :

You need for Querystrings like the one you already have. Create another table for resultstrings and create another one to link querystrings and resultstrings. Dont forget foreign keys.

Upvotes: 1

Related Questions