Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods.

Option 1: Serialize the array as JSON/PHP serialized array and store it in a 'meta' column.

   id  |  name   | meta  
1       Bob       {"city":"GoTown","birthdate":"1980\/8\/14"}
2       Alice     {"city":"Streamville","birthdate":"1986\/6\/6"}

Option 2: Store keys and values together in a stack.

user_id  |    key   |   value   
1         name       Bob
1         city       GoTown
1         birthdate  1980/8/14
2         name       Alice
2         city       Streamville
2         birthdate  1986/6/6

Option 3: 3 tables: entites, keys, values. Store each key name only once.

user_id  |   name   
1         Bob
2         Alice

key_id   |   keyname   
1         city
2         birthdate

user_id   |   key_id   |   value
1          1            GoTown
1          2            1980/8/14
2          1            Streamville
2          2            1986/6/6

Are there any pitfalls with using any of these strategies? I'd like to eliminate some of them if they have insurmountable disadvantages.

share|improve this question

6 Answers 6

up vote 1 down vote accepted

Some really nasty problems would happen with these tables (idea)

1) Data Redundancy (duplicated data) what you need to keep in synchronisation (and you do you check this because MySQL doesn't have an nice JSON to records function???)

2) You can't force correct values on the database (say goodbye to data integrity and hallo to shit in is shit out data) example key birthdate could have value "hello"

And you need some kind of Pivot query to fetch the key/values

If you really need an dynamic key values (EAV) storage there are some more options

Maybe MySQL 5.6 NoSQL 
http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html#nosql
http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html
(I Really need to try this sometime)

XML  -> query with xpath
RDF
NoSQL  like mongoDB
MariaDB see https://mariadb.com/kb/en/dynamic-columns/ and mariadb supports to json... 
share|improve this answer
    
Accepted this one for focusing on the 'pitfalls' question, thanks everyone! –  slothello Oct 15 '13 at 18:48

From your sample data i actually see a schema for the table

UserID | UserName | City | Birthdate

If your using a relational database with the right indexing a search on that would be very fast. If some values could be null then allow null-able columns. A key value store is great in situations but can become very cumbersome when it comes to data validation. If you are using a relational database product put it to work and let it do the validation on the input side with properly typed columns. If you really want to use a key value store a NoSql product optimized for them would probably be the better way to go.

EDITED: added properly typed in front of columns. Use a datetime datatype column for birth date and so on.

share|improve this answer

Given your requirements (MySQL and "schema-less"), I think it might be worth considering Option 1 with a twist. Consider XML instead of JSON.

Why? Because MySql doesn't give you an easy way to process JSON, but it does with xml.

Consider this:

id  |  name   | meta  
1      Bob      <city>GoTown</city><birthdate>1980-08-14&</birthdate>
2      Alice    <city>Streamville</city><birthdate>1986-06-06<birthdate>

Now you can do a sql query using xpath expressions on the content of the meta field. Like so:

SELECT name, extractValue( meta, '//birthdate' ) as birthdate
from tbl

You can even do selects based on the the city (which of course will be VERY inefficient since they will have to parse the contents of the fields that match the where clause, but:

SELECT name<BR>
from tbl<br>
where id < 100
having extractValue( meta, '//city' ) = 'Streamille'

You can even use UpdateXML (another MySQL function) to make changes to the content of your field all within MySQL.

While I know my response is late, I hope someone finds this useful / interesting. :)

share|improve this answer
    
Sounds like a nice compromise, I never thought of xpath in MySQL, thanks for the tip! –  slothello Aug 21 at 20:52

If you may need to seach all customers with birthday between 08/10 and 08/17, then option 1 is not suitable.

Options 2 and 3 are similar. Personally I prefer option 3 because I like to search or group by integer codes rather than by texts.

And by the way. I advice to store months and days as 2 numbers. Then you can sort them as strings and it still will be correct: 1970/10/10 will be after 1970/08/08, but it would be before 1970/8/8.

share|improve this answer
    
Nice tip with the date, IF you're forced to save a human readable date that is - normally it would be better to use unixtime in DB as mentioned by @capnhector. –  slothello Oct 15 '13 at 18:50

If you are not firmly settled upon using MySQL, you may consider DB2 as an option. It has native support for JSON data, which allows you to query and manipulate JSON fragments easily (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.json.doc/doc/c0061316.html).

Another alternative is XML. Again, DB2 has native support for it, including indexing of XPath expressions for quick searches. (http://publib.boulder.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.xml.doc/doc/c0022308.html)

DB2 Express-C is the free edition, with no restriction on production use or database size.

share|improve this answer

Have you consider NoSQL?

Because I work for a NoSQL vendor, I am a bit partial but I was trying to take your problem definition and view it from a lens of what technology is best. This description is ideal for NoSQL given it supports unstructured data and all the options you shared are doable in NoSQL. In my opinion, option 3 is pretty attractive because in a minimal amount of time you can create those tables and be done with it. And if you need to add additional columns, its trivial and you can keep versions of your data. And most of the NoSQL DBs support querying using many different ways and most of these fall within your model.

Most of the NoSQL DBs like FatDB, and RavenDB come to mind and would work well for this for the Windows space. MongoDB, etc, for others.

share|improve this answer
    
Umm, the OP was looking for tips on how to implement "NoSQL" like features in a relational database, ie not a NoSQL database. –  Colin 't Hart Nov 13 '13 at 9:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.