Steax wrote:My rule of thumb is take whatever you, the developer, finds logical. You can always defeat speed/processing power by throwing more oomph at it, but you must avoid making decisions that will slow things down for you in the future.
I've always used the three-table schema. It makes most sense to me, since the whole point of tags is to give users a way to view more items by association. It also provides an easier way to apply metadata to a particular tag, and apply manipulation to them.
In the end, it depends on what you find comfortable and means the least amount of maintenance. I'm a fan of absolute control, hence my schema - it'd be very annoying trying to rename a tag with a TEXT-field schema, and a bit more difficult to extend a two-table schema. I'm also a fan of normalization. Having a logical list of all tags I have gives me a really useful overview over my site structure, and it's easy to deal with them (rather than a long list of separate tags).
I handle categories the same way.
It's not overly difficult to migrate from a two table schema to a three table, assuming you name the tables to account that you might: Just keep the tag name as your tag ID, and then the migration is simply something like:
create table Tags (tagname char(32) primary key, <whatever metadata you want>);
insert into tags (tagname) select tagname from imagetags;
<alter table statements to add referential contraints if needed, make types match etc>
Primary keys should be data that already exists in the table unless there's a very good reason: hiding your file structure is an ok excuse for using a numeric media id, there's no excuse for having a numeric tag id.
73, de KE8BSL loc EN26.