选择主键
我正在做一个项目，我必须制作一个几乎像维基百科一样的网站，但是在制作数据库时我偶然发现了一个问题。
我似乎无法弄清楚我的主键应该是什么。我尽力在下面说明我的表结构，以便您可以看到问题。
name | type | content |
--------------------------------------
John Doe | overview | some text |
----------------------------------------
John Doe | background | some text |
----------------------------------------
John Doe | height | some text |
----------------------------------------
Fred Flintstone | overview | some text |
---------------------------------------
Fred Flintstone | background | some text |
我认为仅仅创建一个 id 列并自动递增它是没有意义的，因为每条记录单独都是无用的。
你怎么认为？是否需要主键？如果是的话应该是什么？
I'm doing a project where i have to make a site almost like wikipedia, but while making the database i've stumbled across a problem.
I can't seem to figure out what my primary key should be. I tried my best to illustrate my table structure below, so you can see the problem.
name | type | content |
--------------------------------------
John Doe | overview | some text |
----------------------------------------
John Doe | background | some text |
----------------------------------------
John Doe | height | some text |
----------------------------------------
Fred Flintstone | overview | some text |
---------------------------------------
Fred Flintstone | background | some text |
I dont think it would make sense to just make an id column, and auto increment it, as each record is useless alone.
What do you think? is a primary key needed? If so what should it be?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我当然会选择
自动增量主ID。这就是它的用途，它使维护和更新表变得更容易，并且也更容易链接到关系设置中的其他表。查找记录时还有性能问题。
总结：
请参阅：http://code.openark.org/blog/mysql/reasons-to-use-auto_increment-columns-on-innodb
I would certainly go for an
Auto Incrementprimary ID. That's what its for, it makes it easier to maintain and update the table, and also a lot easier to link to other tables in a relational setup.There is also the matter of performance when looking up records.
To sum up:
See: http://code.openark.org/blog/mysql/reasons-to-use-auto_increment-columns-on-innodb
我建议您创建一个 id 列作为主键，并再创建一个表来映射概述、背景、高度等记录，
尽管这种类型的模式尚未完全规范化。否则，您可以为概述、背景和高度制作不同的表格，然后为它们创建一个像上面这样的关系表。
I would suggest you create an id column as primary key and create one more table that will map the records like overview, background, height
though this type of schema is not fully normalize. other wise you can make different tables for overview, background and height and then create a relation table like above for them.
在进行更多工作之前，请先访问 mediawiki.org。
在示例数据中，{name, type} 唯一标识每一行。但你的样本数据不具有代表性。名称在现实世界中并不唯一，因此您可能需要其他方法来识别人员。仅仅添加一个自动递增的 ID 号作为主键是行不通的，因为它允许这样的废话。
有多少个不同的人？ （有 3 个。 ID 号为 1 和 3 的 John Doe 是同一个人。）对 {name, type} 的 UNIQUE 约束也无济于事，因为这将只允许一个名为“John Doe”的人出现在数据库。
那么您还知道什么可以用来识别人员呢？电子邮件？ （不要在这里寻找灵丹妙药。没有灵丹妙药。甚至电子邮件地址都可以共享。您只是在寻找比“名称”更好的东西。）
Before you do any more work, take a look at mediawiki.org.
In your sample data, {name, type} uniquely identifies each row. But your sample data isn't representative. Names aren't unique in the real world, so you probably need some other way to identify people. Just adding an autoincrementing ID number as the primary key won't work, because it allows nonsense like this.
How many distinct people are there? (There are 3. The John Does with id numbers 1 and 3 are the same person.) A UNIQUE constraint on {name, type} won't help, either, because that would allow only one person named "John Doe" in the database.
So what else do you know that you can use to identify people? Email address? (Don't look for a magic bullet here. There isn't one. Even email addresses can be shared. You're just looking for something better than "name".)