Is ID column always required in SQL?

Question

to be more specific, I've created a news module with a tag system. As each tag is unique (you are not allowed, as an admin, to create 2 identical tags), is the id column still useful? I guess no, but I was wondering about the performances.

id | mews_title | date ... <-------------> news_id | tag_id <----------> id | tag_name

VS

id | mews_title | date ... <-------------> news_id | tag_tag_name <----------> tag_name

Thanks a lot!

Answer 1

The performance difference is insignificant.

Advantages to using a numeric id for the tags in your example would be:

• to make the intersection table somewhat smaller because integers are smaller on average than a string
• to allow changing the spelling of a tag name by updating one row instead of many rows

These may not be important considerations for your case. So no, it's not required to use a numeric id.

I also wrote about this in a chapter titled "ID Required" in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

Answer 2

You don't even need a table of tags. Just a table with

news_id | tag_name

This is a many-to-many mapping between news_ids and tags. Since there is no tag table, the issue of duplicates vanishes. The same tag (tag_name) can be associated with many news articles, but that is what you want.

INSERT INTO Tags (news_id, tag_name) -- change this to INSERT IGNORE INTO Tags (news_id, tag_name) so you don't have to worry about getting the same news article tagged the same way twice. And have

PRIMARY KEY(news_id, tag_name), -- uniqueness and lookup tags for one article
INDEX(tag_name, news_id)  -- For finding all articles for one tag