I have two logical entities in my database—call them Place and Thing—and I would like to be able to add tags to instances of both kinds. I'd like those tag sets to be shared, i.e., I want one global tagspace, not one for places and another for things. This is easy to do in an OO language (multiple inheritance, mix-ins, whatever), but I'm storing my entities in a relational database. What should I do?
Let's make it a bit more concrete. My tables are:
CREATE TABLE Place(id INTEGER, name TEXT, geocode TEXT);
CREATE TABLE Thing(id INTEGER, name TEXT, weight REAL);
SQL doesn't let me do something like this
CREATE TABLE Tags(tag TEXT, foreign_id INTEGER);
...lots of inserts into various tables go here...
SELECT * FROM (Place OR Thing) whatever, Tags WHERE (Tags.tag = "purple") AND (Tags.foreign_id = whatever.id);
because the result set of a query has to have a uniform shape: if this was
allowed, I could wind up with a mixed bag of results in which some records were (id, name, geocode) and other were (id, text, real).
So what should I do instead? I could fake inheritance:
CREATE TABLE Parent(id INTEGER, name TEXT);
CREATE TABLE Place(parent INTEGER, geocode TEXT);
CREATE TABLE Thing(parent INTEGER, weight REAL);
with the union of Place.parent and Thing.parent being exactly equal to the set Parent.id, and then match tags to actual items via parent ID, but then every other bit of code would become more complex just for the sake of tagging, since I would have to repeatedly stitch together the parent-ish and child-ish bits of Place and Thing. Alternatively, I could maintain two separate tagspaces, but that will quickly become three, then five, as the number of taggable entities in the system grows. What's your
preferred solution, and why?