数据库设计需要专家意见
我希望得到有关网站数据库架构的专家的建议。
我有一个分类列表网站的数据库设计，目前是在 MyISAM 引擎中完成的，因此没有关系、键等。
请查看当前设计的 ER 模型：
我需要知道是否我在右边跟踪数据库设计...（电子邮件和密码字段在用户和广告表中都是多余的，因为第一个计划不需要用户注册，但该计划最近发生了更改。所以，请忽略这一点，因为我将删除广告表中的这些字段）
预计该网站在启动后的几个月内每天至少有 100,000 次页面浏览量。
请提出您对此设计的看法以及可以做得更好的建议。
另请建议用于此类站点的最佳数据库引擎（InnoDb 或任何其他...）。
请就该数据库的最佳存储引擎提出建议，同时考虑性能、负载、数据库完整性（如果需要，因为它目前是在 MyISAM 引擎上设计的，因此没有数据库完整性）以及此类站点需要考虑的其他事项。
我知道特定的数据库问题只有在现实中发生时才能处理，但至少我想朝着正确的方向开始。
预先感谢您的所有帮助。
I would appreciate advice from experts on a Database schema for a website.
I have a database design for a classifieds listings site that is currently done in MyISAM engine so there are no relationships, keys etc.
Please have a look at the ER Model for the current design:
I need to know if i'm on the right track with the DB Design... (The Email and Password fields are redundant in both Users and Ads tables since the first plan was not to require user registration but that plan was changed lately. So, please ignore this bit since i'll remove these fields from Ads table)
It's expected that the site has the potential to do at least a minimum of 100,000 page views a day within a few months from launch.
Please advice about what do you think about this design and what could be done better.
Also please advice on the best Db Engine to use for such a site(InnoDb or any other...).
Please advice on what would be the best storage engine for this database keeping in mind the Performance, Load , Db Integrity(if needed since it's designed on MyISAM engine currently and hence no Db integrity) and whatever else there is to consider for such a site.
I know specific database issues/problems can only be handled only when they occur in reality, but at least i want to make a start in the right direction.
Thanks in advance for all your help.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为什么哦为什么你要使用MyISAM？据我所知，您并不是在谈论高性能网站，那么为什么要使用它？？？使用 InnoDB。
数据完整性不是一个选项，因此，如果您不是专家，您可能不会考虑在应用程序中实现它的逻辑，因此您应该真正考虑使用 FK。
仅查看您的 ER 模型（它甚至不是 ERM，因为您没有定义关系，因此没有 R，它更像是 EM），我们不能说那么多。我们需要一个故事，而这个故事看起来很大。
每天 10 万次点击量微不足道，在这种情况下会产生误导。每秒的最高预期速率是多少？必须能够维持这个水平多久？
该模型还缺少数据类型。即使对于最小的分析，这些也不是可选的。
这样的事情通常需要几天的时间来讨论和分析，我发现这里有点过分了，特别是考虑到信息的匮乏。
Why oh why would you use MyISAM? From what I see, you're not talking about a high performance website, so why use it??? Go with InnoDB.
Data integrity is not an option, so if you're not an expert you probably didn't think about implementing logic for it in your application so you should really consider using FK.
Only looking at your ER model (which is not even an ERM since you have no relationships defined so there is no R, it's more of a EM) we can't say all that much. We need a story, and the story seems big.
100k hits a day is peanuts and in this case misleading. What's the highest expected rate per second? For how long must it be possible to sustain this level?
This model is also missing the data types. Those are not optional for even a minimal analyzes.
Something like this usually takes days to discuss and analyze, I find it a bit over the edge over here, especially given the scarcity of information.
一目了然。 。 。
我们在图表中看不到主键、唯一约束或类似内容。他们的缺席令人不安。
几乎每张桌子都有一个ID号。其中一些仅称为“id”，而其他一些则具有更具描述性的名称。 更具描述性是这两个想法中更好的一个。
没有其他唯一约束的表中的 ID 号通常是错误的。 （通常，但并非总是如此。）例如，表“cats”可能需要对“catname”进行唯一约束。否则，id号只能识别一行，而不是一只猫。 （这意味着您可以有两行或更多行具有相同的“catname”。）
表 adxfields 和 subcatxfields 看起来就像一场等待机会的灾难。表“subcatxfields”看起来像是某种 EAV 反模式。在继续阅读之前，请仔细查看 Bill Karwin 关于数据库反模式的幻灯片那条路。 （EAV 从幻灯片 16 开始。）
Subcat 定价可能应该移至单独的表中。
有一个国家/地区代码国际标准。您最好使用标准国家/地区代码而不是您自己的身份证号码。使用人类可读的代码意味着您通常可以消除连接。 （通常，但并非总是如此。）
表“城市”没有足够的列来识别城市。 （美国有很多城市名为“华盛顿”。）
时间足够了。 。 。
At a glance . . .
We can't see primary keys, unique constraints, or anything like that in your diagram. Their absence is troubling.
Almost every table has an id number. Several of them are just called "id", and others have a more descriptive name. More descriptive is the better of those two ideas.
An id number in a table that has no other unique constraints is usually a mistake. (Usually, but not always.) For example, the table "cats" probably needs a unique constraint on "catname". Otherwise, the id number will only identify a row, not a cat. (And that means you can have two or more rows with the same "catname".)
The tables adxfields and subcatxfields look like a disaster waiting on opportunity. The table 'subcatxfields' looks like some kind of EAV anti-pattern. Look carefully at Bill Karwin's slideshow on database anti-patterns before you go down that road. (EAV starts on slide 16.)
Subcat pricing should probably be moved to a separate table.
There's an international standard for country codes. You're better off using the standard country codes instead of your own id number. Using a human-readable code means you can usually eliminate a join. (Usually, but not always.)
The table "cities" doesn't have enough columns to identify a city. (There are lots of cities named "Washington" in the USA.)
That's enough time . . .