CODEWITHSUNDEEP
phpmysql
denislexic
denislexic

Reputation: 11362

MySQL flat vs key/value pair

I'm developing a sort of intranet wall for people to post information. I've been using normal DB structure for some time, ie:

user_id / username / email / password / name / ... / backgroundColor / ... / mood / ...

A new partner joined the project and he wants to switch to a key/value database to enable more flexibility in the database, ie:

user_id / keyName / value

Being a noob, I understand what he means and how it is more flexible, but I'm worried it'll increase the number of queries to the database / rows and will slow it down. In addition to that we'll need some pretty general type to make sure everything fits.

Can anyone tell me the advantages and disadvantages of using such a DB structure and which one you would recommend using? Does this work for small websites, but is it going to cause problems when we get a lot of traffic and users?

I've seen other posts, but they don't talk to me, ie: Non Relational Database , Key Value or flat table , Using a relational database and a key-value store in combination

Thanks for any help.

Upvotes: 1

Views: 1114

Answers (2)

James Anderson
James Anderson

Reputation: 27478

Just think about it. For a really simple use case such as get eight basic "attributes" for a given userid. Currently this is a single query in a key/value schema this would require eight queries like SELECT ATTR WHERE KEY-NAME = "USERNAME" AND USERID = "XXXXXX";.

Think about more complex use cases such as "Give me all users with 'mood' happy and background color 'pink'". This would be a simple select in your current schema. In a key/value schema this would involve complex joins and sub-queries.

No-Sql scales well because it doesn't do much. By switching to "key/value" schema you lose masses of functionality in exchange for a speed up on some very simple accesses.

Upvotes: 1

StilesCrisis
StilesCrisis

Reputation: 16290

If you go that route, MySQL isn't really the best choice anymore. There are other NoSQL products which are meant for working with less structured key-value data.

The advantage of NoSQL is that it scales extremely well to super-large sites; the disadvantage is that you lose a lot of the advanced stuff that SQL can do very easily (complex queries, joins, etc.). If you are getting a lot of benefit from advanced SQL functionality, it's probably better to stick with what you've got. If you're only ever looking up things in one table at a time and always using simple keys to access the data, and you're worried that you will one day hit the limits of MySQL, NoSQL would eventually allow for higher levels of scalability in the long term.

Upvotes: 1

Related Questions