2

I'm pretty new to working with SQL but I've come across this question on Stack Overflow for working with tags.

Recommended SQL database design for tags or tagging

This led me to create the following tables in my database:

Doc

---------------------------------------------------------------
| Doc Id (PK)(int) | Doc Title (varchar) | Doc Link (varchar) |
---------------------------------------------------------------
| 1                | Printing            | http://example.com |
---------------------------------------------------------------
| 2                | Format              | http://example.com |
---------------------------------------------------------------

Tag

--------------------------------------
| Tag Id (PK)(int) | Title (varchar) |
--------------------------------------
| 1                | print           |
--------------------------------------
| 2                | guide           |
--------------------------------------
| 3                | support         |
--------------------------------------

DocTag

---------------------------------
| DocId (int) | TagId (varchar) |
---------------------------------
| 1           | 1, 3            |
---------------------------------
| 2           | 2, 3            |
---------------------------------

However, I cannot create foreign keys between the DocTag table and the other two because I need to create an array of sorts in the TagId of DocTag because one doc can have many tags. I am getting an error when trying to create the primary key and I assume it's because of the varchar variable in TagId.

What are some suggestions for overcoming this?

Community
  • 1
  • 1
Mr.Smithyyy
  • 2,157
  • 12
  • 49
  • 95
  • 1
    **Do not store data like that** or it will come back to haunt you in near future. You'll be looking for string functions and other sorceries to join tables and then, you'll know. Better keep the tables normalized. – Gurwinder Singh Feb 04 '17 at 18:08

1 Answers1

1

DocTag

---------------------------------
| DocId (int) | TagId (int)     |
---------------------------------
| 1           | 1               |
---------------------------------
| 1           | 3               |
---------------------------------
| 2           | 2               |
---------------------------------
| 2           | 3               | 
---------------------------------

Foreign keys must match the data type of the column they reference.

Each foreign key reference must be a single value. No comma-separated lists.

Put each TagId value on a row of its own, even though you have to repeat the DocId.

See my answer to: "Is storing a delimited list in a database column really that bad?"

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Wow, I can't believe I didn't think of that. For some reason I convinced myself that each DocId had to be unique. Thank you very much for the answer and example. – Mr.Smithyyy Feb 04 '17 at 18:25
  • It would make sense that to make the *pair* of columns unique. Only one row per DocId/TagId combination. – Bill Karwin Feb 04 '17 at 19:11