查找表：有更好的做法吗？
我想知道在设计查找表时是否有更好的实践或原则。
我打算设计一个可以服务于许多不同情况的抽象查找表。
例如，我将我的查找表称为masters and Slaves表，
CREATE TABLE IF NOT EXISTS `masters_slaves` (
`mns_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`master_id` varchar(255) DEFAULT NULL COMMENT 'user id or page id',
`slave_id` varchar(255) DEFAULT NULL COMMENT 'member id or user id or page id',
`cat_id` varchar(255) DEFAULT NULL COMMENT 'category id',
`mns_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`mns_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`mns_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
因此该查找表可以服务这些类型的关系，例如
Admins and Members
Admins and Pages
Admins and Posts
Post Categories and Posts
Page Parents and Pages
etc
masters和中的 表将描述和区分这些类别。例如，cat_id Slavescat_id 1 是管理员和成员等等。
我将插入：
- admin id 到列中
master_id和会员 id 写入slave_id列 - 父页面id插入列
master_id和子页面 id 成slave_id列 - 将类别 id 发布到列中
master_id和页面 id 到slave_id列 - 等
但我确定是否应该这样做：
- 这是一个很好的查找吗 桌子练习还是应该做更多创造更多 比针对不同的一个查找表 关系？
- 如果我可以像这样查找表 这只是一个查找表 对于所有人，我会产生什么后果 将来有吗？这鞋底会不会 查找表将被过度填充 当我的网站内容增长时？
- 我想到的另一件事是—— 标签系统不是一个查找表吗 解决方案也？
谢谢。
I wonder if any better practice or any principle when come to design a lookup table.
I intend to design an abstract lookup table which can serve many different situations.
For instance, I call my lookup table as a masters and slaves table,
CREATE TABLE IF NOT EXISTS `masters_slaves` (
`mns_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`master_id` varchar(255) DEFAULT NULL COMMENT 'user id or page id',
`slave_id` varchar(255) DEFAULT NULL COMMENT 'member id or user id or page id',
`cat_id` varchar(255) DEFAULT NULL COMMENT 'category id',
`mns_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`mns_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`mns_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
so this lookup table can server the relationship of these types like,
Admins and Members
Admins and Pages
Admins and Posts
Post Categories and Posts
Page Parents and Pages
etc
the cat_id in masters and slaves table will describe and differentiate these categories. for instance, cat_id 1 is Admins and Members and so on.
And I will insert:
- admin id into the column of
master_idand member id intoslave_idcolumn - parent page id into the column of
master_idand child page id intoslave_idcolumn - post categories id into the column
ofmaster_idand page id intoslave_idcolumn - etc
But I am sure about it whether I should go for it or not:
- Is this a good lookup
table practice or should do many create more
than one lookup tables for different
relationships? - If I can do the lookup table like
this, which is only one lookup table
for all, what consequences I will
have in the future? Will this sole
lookup table will be over populated
when my site content grows? - Another thing come to mind is that -
isn't the tag system a lookup table
solution too?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这听起来非常像反模式一个真实的查找表。谷歌一下！
以下是我在不到 1 分钟内想到的一系列不好的事情：
我可以很容易地想出更多，但我认为它并不真正需要；）
当一个人没有太多数据库经验时，重用东西并使用“通用”结构感觉像是一件好事，但数据库很少从中受益。
对您的关系使用单独的表。最终，无论如何你都会这么做。
It sounds an awful lot like the anti-pattern One True Lookup Table. Google it!
Here is a list of bad things I came up with in less than 1 minutes:
I could easily come up with more, but I don't think it's really needed ;)
When one doesn't have much database experience, it feels like a good thing to do, to reuse stuff and use "common" structures, but databases rarely benefit from it.
Use separate tables for your relationships. Eventually, you will do that anyway.
根据我的经验，最好为每个类别创建一个查找表。
以下是我所看到的好处：
我认为拥有几张小桌子比拥有一张大桌子更有利。
From my experience it is better to create a lookup table for each category.
Here are the benefits as i see them:
I think there are more pros for having a few smaller tables than one big one.