0

I wanted to use a relational database(MySql) to store my data as key-value pair.

  • I would be getting no. of key-value pairs dynamically.
  • I can create a simple table to store them in separate columns.
  • Values can be of type- int, varchar, text or date.

The problem which I am facing is:

When I need to run a query on key whose value should be an integer and I need to use and greater than or less than query with it. Same case when I need to use between query with date fields.

How can I achieve it?

------------------------------------------------Edit---------------------------------------------------

For greater clarity, I am providing the background for this question which I have divided into three parts: 1. Data 2: Use Case 3. Possible Designs

1. Data

Suppose I'm creating data store for census of a country**(Just an example)**. Fields for storing data would be different for male, female, boy or girl and also it will vary according to the person's profession. The number of fields depends on the requirement which can increase up to 500 or more.

2. Use Case

  1. Show a paginated list of persons whose monthly income is between $7000 to $10000. User can click on any page number and the database should directly fetch the data for that page number. For example, if we are showing 10 results in a page and user clicks on the 5th page then we should show him the list of the person's from 40 to 50.

  2. Some of the values belonging to a particular group store description which can have large data. So they should be stored as TEXT.

3. Possible Designs

  1. I can create a separate table for each different type and store their data in respective fields. But the problem I'm thinking about this approach is that MySQL table has a maximum row size limit of 65,535 bytes. Going by this approach and storing all data horizontally might cross the max size limit. As the number of fields are not fixed and can change as per requirement.

  2. Instead of storing data horizontally I can store them vertically using Entity Attribute Value design(key-value pair). For now, the increase in the number of rows due to this design is not a problem. Using this I can store data of all male, female or child in the same table. But the problem with this approach is:

    • I will lose the Datatype of certain important fields. I can not query and get the list of persons whose income is more than 1000.

    • For storing data or all fields in single Value type, I need to make it varchar. But some fields store large data which requires TEXT as the type.

    Considering the above problem, I thought that instead of creating only one value field, I will create multiple value fields like value_int, value_varchar, value_date or value_text.

DB structure

For this problem, I will be using MySQL and cannot change the DB due to certain restrictions. So I am looking for a design with MySQL only.

Going by key-value approach is a good idea or not? Or any other possible design which can be used?

Pranav
  • 167
  • 2
  • 12
  • One of the qualities of a key-value data store is that you can look up values by their key, not by their value. It really doesn't sound like you're designing a key-value system at all. You are designing a relation. – Bill Karwin May 20 '19 at 15:24
  • it seems impossible. i think, you can filter your data after get – Farhad Misirli May 20 '19 at 15:27
  • As mentioned in the other comments, traditional key-value pair storage is all about searching and filtering by key not value. In such scenario the best option would be to pull by key and then sort/filter the values in the app server. Having said that, you are using mysql, so you can do whatever you want like creating a table(s) that has two columns one would be the primary key and the other is the value which could be anything you want. Then you can use traditional sql constructs to pull the necessary data – camba1 May 20 '19 at 17:36
  • 1
    Follow the [entity-attribute-value] tag to lots of discussions of such. Condolences. – Rick James May 21 '19 at 05:12
  • Thank you for your answers. I have edited the question, added an example and further description of the approaches I have thought. Can you please look into it once again. – Pranav May 21 '19 at 07:53
  • Thank you @BillKarwin you are right. I should query data by key instead of value. But how to design the database when we have a lot of variable fields(keys) and at the same time certain grouping or comparison needs to be done on that data based on value. – Pranav May 21 '19 at 07:56
  • https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m/695860#695860 – Bill Karwin May 21 '19 at 14:44
  • https://www.slideshare.net/billkarwin/extensible-data-modeling – Bill Karwin May 21 '19 at 14:44
  • https://www.slideshare.net/billkarwin/how-to-use-json-in-mysql-wrong – Bill Karwin May 21 '19 at 14:45

1 Answers1

1

In very general terms, if you know the entities and attributes of your problem domain, and the data is relational, I'd use a relational schema (your "possible design 1"). If you actually encounter problems with maximum row width, your problem domain might contain logical subgroupings of attributes, so you can split them into separate table.

For instance:

Person (id, name, ...)
Person_demographics (person_id, age, location, ...)
Person_finance (person_id, income, wealth...)

If you don't know the entities and attributes in advance, I recommend using MySQL's JSON support. or XML support. This gives you access to much better query options than EAV.

The problem with EAV-like solutions in your scenario is that any non-trivial queries end up being incredibly complicated - "find all responses where salary is between x and y, and the age is z, in locations (a, b, c)" turns into a horrible mess of SQL, but with XPath this is pretty straightforward.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • Thank you @NevilleKuyt for the response. JSON Support looks good for my problem. I will check again if all my scenarios are covered or not. – Pranav May 21 '19 at 14:05