One table SQL column, column ID is crazy?

Question

I am developing a database that will be used for internationalized content. One of the features is the tag system, similar to the one that has a stack overflow. This is what I have:

TABLE tags tag_id | int(11) unsigned | NOT NULL | PRI | auto_increment TABLE tag_translations | tag_translation_id | int(11) unsigned | NOT NULL | PRI | auto_increment | fk_language_id | int(11) unsigned | NOT NULL | MUL | | fk_tag_id | int(11) unsigned | NOT NULL | MUL | | tag_name | varchar(255) | NOT NULL | UNI | TABLE tag_relationships | tag_relationship_id | int(11) unsigned | NOT NULL | PRI | auto_increment | fk_tag_id | int(11) unsigned | NOT NULL | MUL | | fk_solution_id | int(11) unsigned | NOT NULL | MUL | 

First of all, is it wise to have a tag table containing only an identifier? Secondly, how can I populate this column with a single field that is an identifier for auto-increment?

Answer 1

Q1: No. I do not see the point of the tags table. It doesn't seem to mean anything. If you cannot explain the semantic meaning of the row in this table (what does the entry in this table do in the real world?), Then it probably does not belong. I suspect that it is only there to give you auto_increment , which will not be a good answer. You can assign your own identifiers.

Q2: already answered.

I also don’t see what tag_translation_id and tag_relationship_id . Normal use of auto_increment ?

I think I would do for a basic structure:

 create table tag_translations ( tag_id int not null, language_id int not null, tag_name varchar(255), primary key (tag_id, language_id) ); create table tag_relations ( tag_id int not null, solution_id int not null, primary key (tag_id, solution_id) ); 

To which I would add metadata and indexes as needed. Two column indexes are very good for joins, such as tag_relations ' Using index optimization:

Index usage

Information about a column from the table was obtained using only the information in the index tree without the need to make an additional attempt to read the actual series. This strategy can be used when the query uses only columns that are part of the same index.

Btw, for internationalized systems, 255 is not a magic number for the varchar field length unless you adhere to single-byte encodings. If you are using UTF-8, take a look at the manual and think about it, especially if you are going to index this column.

Answer 2

As I said in my comments on OP, I had exactly the same problem a few years ago. However, I used SQL Server, not MySql, but the problem was the same.

Unfortunately, the only solution I found was to add extra columns to the tag table. I decided to add a DateCreated column, which ended up being useful.

Answer 3

 INSERT INTO tbl_name () VALUES();