Thanks Helen,

>>Design 3) makes it easy to search/report but more complex to add items.
> What's complex about it?

Well some customers prefer to interact with the database using their own
systems (pumping data in/out etc). They have reasonable DB knowledge but
they are not experts. For them to add an ITEM with tags they would have to
a) insert the ITEM (using a known new item id), b) check for existing tags
and get their id's, c) add all new tags with new id's, d) for each tag
insert a tag/ITEM mapping. A single stored proc call or insert would be much
easier for them to manage! I guess the other advantage is that it is easy to
manipulate with simple statements in a DB migration script.

> I have an executable SP to which I send a keyword and a document id
> as arguments. It simply inserts a record in the intersection table.

I'm not sure if I understand your design correctly. Do you have two tables,
DOCUMENT and KEYWORD with KEYWORD having a DOCUMENTID FK? Or do you have
three tables, DOCUMENT, KEYWORD, INTERSECTION with KEYWORD containing only
unique and INTERSECTION having a FK to DOCUMENT and KEYWORD? It sounds like
the latter but what if the keyword does not exist? Does your SP do that?

If I store tags in a separate table (and I'm now sure that I will) I will
create a similar SP but it would likely perform the job of inserting new
tags to the TAG table as well as inserting the mapping. That at least would
make b/c/d) above a single step.

I'm thinking now of having:

Table ITEM: ID, TAGNAMES VARCHAR(...), ...
Table TAG: ID, TAGNAME VARCHAR(...)
Table ITEMTAGMAPPING: ID, TAGSETID, ITEMID, TAGID
SP_UPDATETAGS(ITEMID, TAGNAMES)
SP_ADDTAG(ITEMID, TAGNAME)

Note the TAGNAMES column in the ITEM table to preserve the original tags
entered (like preserving the document content). After insert/update triggers
on the ITEM table call SP_UPDATETAGS(NEW.ID, NEW.TAGNAMES). SP_UPDATETAGS
(in brief) gets a unique TAGSETID, splits TAGNAMES, calls SP_ADDTAG(ITEMID,
TAGSETID, TAGNAME) for each which checks for the existence of an existing
tag by name - if not found it adds it - and checks for existence of existing
mapping to that tag - if found not found it adds it with the TAGSETID - if
found it updates it with the new TAGSETID. Once all tags added/updated
SP_UPDATETAGS deletes tags for the ITEM where MAPPING.TAGSETID <> :TAGSETID.

This has the best of both worlds (easy to insert rows, better table
structure for easy search and reporting).

Too much work?

Thank you again for your input.

Regards,

Jarrod Hollingworth
Complete Time Tracking
http://www.complete-time-tracking.com/