CODEWITHSUNDEEP
mysqldatabasedatabase-designentity-attribute-value
Pranav
Pranav

Reputation: 177

How to use Key-Value pair in Relational database(MySql)?

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

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?

Upvotes: 0

Views: 2144

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29629

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.

Upvotes: 1

Related Questions