CODEWITHSUNDEEP
mysqldatabasenosql
arhud
arhud

Reputation: 69

Database choice when number of columns varies

If i'm considering storing something like the list of all the movies a person likes on facebook, the number of columns per person isn't constant. I've read some articles that are not in favor of NoSQL. Is there a MySQL hack that would let me do something like this?

Upvotes: 0

Views: 91

Answers (3)

Misguided
Misguided

Reputation: 1312

There is no hack needed, you just have to plan your schema in order for it to be relational.

For example, suppose you had a table called Users, and it had the following fields: id, name, email, password. Its graphical representation would be something like this:

id | name |    email    | password
------------------------------------
1  | John | [email protected] | <md5 hash>
2  | Mark | [email protected] | <md5 hash>
ETC...

Then, since you want to store the movies the user liked, you could have yet another table, called UsersMovies, containing fields id, user_id, movie_id:

id | user_id | movie_id
-----------------------
1  | 1       | 187
2  | 1       | 486231
3  | 2       | 9843
4  | 1       | 457
ETC...

This second table would contain the relationships between each user and one movie he likes. This would mean that, for user_id 1, you would know he likes movies with the ids, 187, 486231 and 457. This movie_id could hold the facebook's movie id, or whatsoever identifies this movie as such.

Adopting this model, fetching all of the movies for X user would be a simple as: SELECT * FROM UsersMovies WHERE user_id='X', X being the user's id in the user table, while fetching all users who like the movie X would be as simple as SELECT user_id FROM UsersMovies WHERE movie_id='X'

You can read more about this from this place.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230461

You can do it with traditional relational databases (like MySQL).

Since it's a schema-ful database, you can't have different set of columns for each row. However, you can have one column (let's call it extra) which will be of type TEXT (or BLOB).

You can take all your variable columns, serialize them to JSON (or XML or Protobuf or MessagePack or whatever) and write to this special column.

This way you can write arbitrary data to a record and still use MySQL. However, you just lost most of RDBMS functionality: indexing, querying and efficient updates of that data. It's now a key/value store with persistence!

At this point you might be better off with a true NoSQL solution. Amongst the better ones I can name MongoDB. It's a schema-less database (which means that every row (or document, in their terminology) can have its own unique set of fields). You can build secondary indexes on those fields and efficiently query documents. See the site for more features and presentations and videos.

Gather as much information as you can and make an informed decision.

Upvotes: 0

Ravenex
Ravenex

Reputation: 621

As I noted in the answer to this question:

insert into two tabels in mysql database at the same time

What your looking to do is create an table to store one -> many relationships. Varying the number of columns isn't possible, what you do is associate multiple rows with the same user.

Upvotes: 1

Related Questions