Traditional SQL vs MongoDB/CouchDB for simple python app

Question

Say I got an traditional SQL structure like so:

create table tags (id PRIMARY KEY int, tag varchar(100));
create table files (id PRIMARY KEY int, filename varchar(500));
create table tagged_files (tag_id int, file_id int);

I add some tags:

insert into table tags (tag) values ('places');
insert into table tags (tag) values ('locations');

And some files:

insert into table files (filename) values ('/tmp/somefile');
insert into table files (filename) values ('/tmp/someotherfile');

and then tag these files:

insert into table tagged_files (tag_id, file_id) values (1,1);
insert into table tagged_files (tag_id, file_id) values (1,2);

Then I can find all the files tagged with the first tag like so:

select * from files, tagged_files where id = file_id and tag_id = 1

But how do I do the same thing using NoSQL solutions like MongoDB and CouchDB?? And what NoSQL project is best suited for something like this?

Answer 1

For MongoDB.

Chances are you'd simply save them with the tags as:

db.Files.save({ "fn" : "/tmp/somefile", "ts" : [ { "t" : "places" }, { "t" : "locations" }] });
db.Files.save({ "fn" : "/tmp/someotherfile", "ts" : [ { "t" : "locations" }] });

Alternative is to save tags separately (ObjectId's are 12 bytes iirc):

db.Tags.save({ "t" : "places" });
db.Tags.save({ "t" : "locations" });
db.Files.save({ "fn" : "/tmp/somefile", "t" : [ { "i" : ObjectId("IdOfPlaces") }, { "i" : ObjectId("IdOfLocations") }] });

In both cases to get a file it's

db.Files.find({ "_id" : ObjectId("4c19e79e244f000000007e0d") });
db.Files.find({ "fn" : "/tmp/somefile" });

Or a couple files based on tags:

db.Files.find({ ts : { t : "locations" } })
db.Files.find({ t : { i : ObjectId("4c19e79e244f000000007e0d") } })

Examples are from the mongo console. And if you store tags with Id's you'd obviously have to read the tags up based on the Id's once you got the file(s).