Leeoniya
04-30-2008, 02:56 PM
i'm working on mass tagging system for my DB. i would like to tag any record with any/multiple tags as needed. sometimes these tags will indicate that extended properties exist elsewhere for the object, in this case there will be an entry in the "xa_table_id" (extra attributes) field.
for this i have 3 tables:
class_tags (classification tags)
tag_id
tag_type (FK on tag_types.type_id)
tag_name
xa_table_id
class_tag_types
type_id
type_name
type_descr
table_index (index of any tables used in tagging)
table_id
table_name
table_decr
class_tag_links (tables/records to tag links)
tag_id (FK on class_tags.tag_id)
table_id (FK on table_index.table_id)
record_id (reference to a PK in the indicated table)
first of all, i know it will be very flexible (this is the intent), but i dont know if it will be easy to make SQL for...or be efficient, it will certainly be less efficient than making additional table fields as i need them, but this can be limiting in that my "tag clouds" can only be as big as how many extra columns i have added to my tables.
and now the main question:
how would i go about selecting a record from a table (say "products")...and also getting the xa_table indicated in the class_tags table.
- select product
- determine which tags apply to it in the class_tag_links table by examining the table_id and record_id fields
- join any xa_tables that apply to the linked tags from the class_tags table.
can all this be done in SQL only?
also, going in reverse: given a tag, or tag list...determine which records in which tables are linked to that tag (and return the table names and record ids)
i can reduce the complexity of the queries perhaps by using the table names directly rather than a "table_id" this would save one JOIN, but will be less normalized, and indexing will be more costly on a varchar column.
thanks,
Leon
for this i have 3 tables:
class_tags (classification tags)
tag_id
tag_type (FK on tag_types.type_id)
tag_name
xa_table_id
class_tag_types
type_id
type_name
type_descr
table_index (index of any tables used in tagging)
table_id
table_name
table_decr
class_tag_links (tables/records to tag links)
tag_id (FK on class_tags.tag_id)
table_id (FK on table_index.table_id)
record_id (reference to a PK in the indicated table)
first of all, i know it will be very flexible (this is the intent), but i dont know if it will be easy to make SQL for...or be efficient, it will certainly be less efficient than making additional table fields as i need them, but this can be limiting in that my "tag clouds" can only be as big as how many extra columns i have added to my tables.
and now the main question:
how would i go about selecting a record from a table (say "products")...and also getting the xa_table indicated in the class_tags table.
- select product
- determine which tags apply to it in the class_tag_links table by examining the table_id and record_id fields
- join any xa_tables that apply to the linked tags from the class_tags table.
can all this be done in SQL only?
also, going in reverse: given a tag, or tag list...determine which records in which tables are linked to that tag (and return the table names and record ids)
i can reduce the complexity of the queries perhaps by using the table names directly rather than a "table_id" this would save one JOIN, but will be less normalized, and indexing will be more costly on a varchar column.
thanks,
Leon