一列SQL表，该列为ID-这很疯狂吗? [英] One column SQL table, the column being an ID - is this crazy?

问题描述

我正在设计一个将用于国际化内容的数据库.其中一项功能是标记系统，类似于Stack Overflow所具有的功能.这就是我所拥有的:

I am designing a database which will be used for internationalized content. One of the features is a tagging system, similar to what Stack Overflow has. This is what I've got:

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 |  

首先，让标签表只包含一个ID似乎合理吗?其次，我如何用唯一的字段作为自动递增ID填充该列?

First of all, does it seem reasonable to have that tags table containing nothing but an ID? Second, how could I populate that column with the only field being an auto incrementing ID?

推荐答案

Q1:否.我看不到tags表的要点.似乎没有任何意义.如果您无法解释该表中某行的语义值(该表中的条目在现实世界中代表什么?)，则该行可能不属于该表.我怀疑只是为了给您auto_increment而已，这不是一个好答案.您可以分配自己的ID.

Q1: No. I can't see the point of the tags table. It seems not to mean anything. If you can't explain the semantic value of a row in that table (what does an entry in that table represent in the real world?), then it probably doesn't belong. I suspect it is only there in order to give you auto_increment, which would not be a good answer. You can assign your own IDs.

第二季度:已经回答.

我也看不到tag_translation_id和tag_relationship_id的用途.习惯使用auto_increment?

I also don't see what tag_translation_id and tag_relationship_id are for. A habitual use of auto_increment?

我认为我要做的基本结构是:

I think what I'd do for basic structure is:

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'd add metadata and indexes as needed. Two column indexes are very nice for junctions such as tag_relations because of the server's 'Using index' optimization:

使用索引

检索列信息 从表中仅使用信息 在索引树中，而不必做 另一种寻求阅读实际内容的方法 排.在以下情况下可以使用此策略 该查询仅使用以下列 单个索引的一部分.

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

顺便说一句，对于国际化的系统，除非您坚持使用单字节编码，否则255对于varchar字段长度并不是一个神奇的数字.如果您使用的是UTF-8，请仔细阅读该手册并加以考虑，尤其是要为该列建立索引时.

Btw, for internationalized systems, 255 isn't a magic number for varchar field length unless you're sticking to single-byte encodings. If you're using UTF-8, look at the manual and give it some thought, especially if you are going to index that column.

这篇关于一列SQL表，该列为ID-这很疯狂吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！