CODEWITHSUNDEEP
mysqlsqldatabase
skyman
skyman

Reputation: 2335

Database schema: Key/Value table or all keys in one record

I guess that this is somewhat of a philosophical question. I need to collect pathology results for a group of patients and store them in a database. In the past I have used a very simple table structure (simplified):

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| ID                | bigint(20)   | NO   | PRI | NULL    |       |
| Updated           | datetime     | NO   | PRI | NULL    |       |
| PatientId         | varchar(255) | NO   |     | NULL    |       |
| Name              | varchar(255) | NO   |     | NULL    |       |
| Value             | varchar(255) | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

More often in schema design I see:

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| ID                | bigint(20)   | NO   | PRI | NULL    |       |
| PatientId         | varchar(255) | NO   |     | NULL    |       |
| Ph_Value          | varchar(255) | NO   |     | NULL    |       |
| K_Value           | varchar(255) | NO   |     | NULL    |       |
| Ca_Value          | varchar(255) | NO   |     | NULL    |       |
| Ph_Value_updated  | datetime     | NO   |     | NULL    |       |
| K_Value_updated   | datetime     | NO   |     | NULL    |       |
| Ca_Value_updated  | datetime     | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

It seems to me that the first design is much more flexible, expandable etc. However, I do wonder about performance hits when the records run to the millions.

The issue with the second is that there may be a couple of hundred fields that need to be recorded on occasions.

I would be really interested to get comments / advice / guidance on this.

Upvotes: 1

Views: 160

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You are absolutely right, the first schema is a lot more flexible: you can add new keys on a live database without changing the schema. However, flexibility is usually bought with the time and/or the space. In this case, it's both: you need more space to store all keys for the same row because the ID is replicated N times, and the joins or orderings required to get the fields together would take time.

There is no reason to pay for flexibility unless you need it. If most of your queries need most of the columns, the second result is the most economical. However, if most of your queries ask for a single column, getting the flexibility may be worth spending the CPU time and the database space.

Upvotes: 1

youssefhassan
youssefhassan

Reputation: 1065

In my opinion, If that name/value pairs won't be changed much so the second option is much better in the terms of space and number of rows.

Also you can have another solution to optimize the first schema , to put the names in another table and just put name_id instead of repeating the same name several times.

The other schema is to have patient table and a table for each value that contains patient_id and value and the table name is the name for that value

Upvotes: 1

Related Questions