ft/tags

[imrishuroy]

Tags Mapping

DB Structure

1. Create Tags Table:
   Fields: tag_id (Primary Key), tag_name

2. Create Users Table:
   Fields: user_id (Primary Key), username, other_user_fields

3. Create Cache Table:
   Fields: cache_id (Primary Key), user_id (Foreign Key), cache_content, other_cache_fields

4. Create User_Tags Table (for Many-to-Many relationship between Users and Tags):
   Fields: user_tag_id (Primary Key), user_id (Foreign Key), tag_id (Foreign Key)

5. Create Cahe_Tags Table (for Many-to-Many relationship between Cache and Tags):
   Fields: cache_tag_id (Primary Key), cache_id (Foreign Key), tag_id (Foreign Key)

CREATE TABLE "tags" (
    "tag_id" INT PRIMARY KEY,
    "tag_name" VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE "user_tags" (
    "user_id" varchar,
    "tag_id" INT,
    PRIMARY KEY ("user_id", "tag_id"),
    FOREIGN KEY ("user_id") REFERENCES users("id"),
    FOREIGN KEY ("tag_id") REFERENCES tags("tag_id")
);

CREATE TABLE "cache_tags" (
    "cache_id" bigserial,
    "tag_id" INT,
    PRIMARY KEY (cache_id, tag_id),
    FOREIGN KEY ("cache_id") REFERENCES  caches("id"),
    FOREIGN KEY ("tag_id") REFERENCES tags("tag_id")
);