How to efficiently store tags for items in relational database?-CodePudding

Generally speaking, is making all attributes in an entity a concat PK possible, or is it a bad practice? A product may have many tags and how do I store these tags for each product? Is it possible to have a table “tags” with 2 attr: productid and tags and make both of them primary key?

Entities:

PRODUCT > productid(PK), name, …………

TAGS > productid(FK), tags (concat PK)

Thanks.

CodePudding user response：

Generally speaking, is making all attributes in an entity a concat PK possible, or is it a bad practice? A product may have many tags and how do I store these tags for each product?

In the most typical use case for tags, a tag may apply to more than one record. For the most normalized design, you've nearly arrived at the best design:

A tag may be used on (may refer to) several products
A product may have several tags

This suggests a many-to-many relationship between Tags and Products. This can be readily implemented with a bridge table. An advantage of this design is that the design of each Tag (and each Product) record does not incorporate a foreign key at all and is therefore simpler. The records of the bridge table describe the relationships between Tags and Products.

CodePudding user response：

Welcome @jun! While this question seems to be more of a recommendation question, rather than something more specific and technical, I'll do my best to help.

To answer your questions:

Yes, it is possible, and sometimes useful, to create a table with two columns and have both columns be composited as the primary key. It is not considered bad practice, generally, and like most things, it depends on what you are doing. Any good practice can be bad if used wrong.

Personally, I would recommend going with a Tags table (ProductId (PK, FK), Tag (PK)), with 1 tag per row. This simplifies inserts/deletes, and makes them more idempotent, which is safer and better if there were possible situations for multiple users to be editing the tags for the same product at a time.

The composite primary key will also ensure that: a) No product has duplicate tags by the same name, and b) an index is automatically created to ensure queries by ProductId are fast.