Jarrod Hollingworth wrote:

> 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?

What is the purpose of ITEM.TAGNAMES?

Note that new FB versions have a new aggregate function list(...) which
concatenates the child items into a comma separated list. You can use
that to get the comma separated tag list easily from ITEM join
ITEMTAGMAPPING join TAG.

It' not really rocket science to use a lookup like your TAG table and
the many-to-many link table ITEMTAGMAPPING. It's pretty basic for
relational databases actually. Getting the new ID for new tagnames is a
bit cumbersome perhaps, but you can encapsulate that and other frequent
user-friendly tasks into a small set of SP:s.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64