Wildfalcon

Thinking and developing in everything – by Laurie Young
Browse: Home / / A Classic Web 2.0 Architecture Puzzle

A Classic Web 2.0 Architecture Puzzle

Posted by Laurie on 8/29/08 • Categorized as General

Time for an open puzzle, answers on a postcard, or preferably in the comments box below

Imagine you are implementing a typical web 2.0 application. It has cool things, like video clips, and maps. Lets keep it simple for now, and say it only has status messages and video clips. The two are unrelated, sharing almost no common features (you can’t play a map, or add a way-point to a video). There is just one common feature that you have to have to concern yourself with, tags.

Both video clips and maps can be tagged, when you see a video clip tagged with “dog” you should be able to click on “dog” to see all the video clips tagged with “dog” as well as all the maps tagged with “dog”

The question is how would you store this in the database. Specifically I’m looking for a database schema, what tables would you use, and what columns? The question is intentionally stripped to its bare minimum, the extension would be to allow lots of other things to be tagged, and to allow tag based queries (such as retrieving all tags related to a given tag) to be executed as fast as possible.

  • Richard
    This is possible in relational terms - but its not clean, and it relies on a schema change whenever you add features.

    Create an umbrella concept - say taggables. Both maps and video clips are taggables. In OO terms they'd implement the taggable interface or be subclasses of the taggable class. Here comes the messy part:

    Create a table called taggables which only contains one column - it is the primary key (obviously) and contains a list of taggable uids. You then need a mapping table from each kind of taggable object to your taggables table (this is analogous to the code you'd have to write for each implementation of the taggable interface). For example a table called taggable_maps which has two columns - both foreign keys - one from maps and one from taggables. The same thing would exist for videos. You can then create your tag tables against that UID in the taggables database.

    Possibly you could do this with a column in the videos table and in the maps table storing the tagableness ID - but I've not got time to do the necessary thinking to ensure that works in all cases. The advantage of seperate tables is that you can add taggableness without altering tables.

    The only thing your database wouldn't do for you is prevent a map and a video being linked to the same taggable ID.
  • Surely thats not what you would tell the client? :p You would have to come up with some way to do it ...
  • Fabre Lambeau
    Not solvable with a pure compliant relational database schema
    You will need to become non-BCNF!
blog comments powered by Disqus