MySQL的数据库架构设计，有建议吗？
所以，有机会做我的第一个“真正的”数据库，只是思考我应该考虑什么......有什么建议吗？
- 选择数据库（使用 MySQL）
- 根据需要选择数据库引擎（使用 MySQL，了解 MyISAM 与 InnoDB）
- 为数据库创建表文档
- 使用列定义
- （选择正确的数据类型）要规范化的模型数据
- 引用完整性：主键（自然或代理）；复合键；外键
这引出了我的主要问题，我如何知道数据库是否足够成功？
So, have the chance to do my first "real" database, just thinking to myself what I should think about... any suggestions?
- Select DB (using MySQL)
- Select DB engine based on needs (using MySQL, know MyISAM vs InnoDB)
- CREATE TABLE documention for DB using
- Column definition (select right datatype)
- Model data to be normalized
- Referential Integrity: Primary Key (Natural or Surrogate); Composite Keys; Foreign Key
Which leads me to my main question, how do I know if the database is a success at fit enough?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
当您列出从想法到工作系统需要回答的一般问题时，我建议将它们组织成
1）逻辑设计（获得一个干净的模型来代表您正在尝试的问题空间） 非常重要模型）
2）物理设计（RDBMS 和存储引擎、确切的数据类型、其他实用和性能相关的决策）
您在两者之间做了太多的混合。当您获得清晰的逻辑模型并了解正在建模的实体之间的关系时，物理建模就不难了。
编辑：
有很多书籍介绍逻辑数据设计的步骤，但通常您会尝试：
需要反复执行其中一些步骤。例如，包含新属性可能会让您去分析新用例。或者，发现矛盾的需求可能会导致发现一个全新的实体。或者，发现冗余可能会导致您发现存在的未记录的流程（并通过重新定义看似重复的属性来证明或解释感知到的冗余）。 ETC...
As you are listing general questions that need to be answered in order to go from idea to working system I'll propose that it is very important to organize them into
1) logical design (get a clean model that represents the problem space you are trying to model)
2) physical design (which RDBMS and storage engine, exact data types, other practical and performance related decisions)
You are doing too much of mixing between the two. When you get a clean logical model and know the relationships between the entities you are modelling then the physical modelling will not be hard.
EDIT:
There are many books that deal with the steps of logical data design, but normally you would try to:
A few of these steps need to go back and forth as you consider different ways of accomplishing certain tasks. For example including new attributes might make you go and analyze a new use case. Or a discovery of contradicting requirement might lead to a discovery of a whole new entity. Or discovering a redundancy might lead you to a discovery of undocumented process that exists (and justifies, or rather, explains percieved redundancy by redefining a seemingly duplicate attribute). Etc...
在定义列之前对数据进行建模并对其进行标准化。即使在选择数据库和表类型之前，这也需要成为您的首要任务，因为它将允许您清楚地了解正在建模的任务。
根据需要选择数据库引擎（使用MySQL，了解MyISAM vs InnoDB）
权衡是“查询性能”与“事务”。在大多数情况下，innoDB 性能足够好，事务的好处超过任何缺点。
创建表文档可在 MySQL 网站上找到。
正如 Unreason 所说，“当你获得一个清晰的逻辑模型并知道你正在建模的实体之间的关系时，物理建模就不难了”。
成功可以通过多种方式来衡量。口袋里有钱，低价硬件上有良好的性能。来自用户的很多愉快的评论......比如 Stackoverflow:)
Model your data and normalise it before defining your columns. This needs to be your first task even before selecting a database and table tiypes as it will allow you to get clarity about the task you are modeling.
Select DB engine based on needs (using MySQL, know MyISAM vs InnoDB)
The trade off is "query performance" v "transactions". In most cases innoDB performance is good enough and the benefits of transactions outweigh any downside.
Create table documentation is available on the MySQL website.
As Unreason says "When you get a clean logical model and know the relationships between the entities you are modelling then the physical modelling will not be hard".
Success can be measured in various ways. Money in your pocket, good performance on low priced hardware. Lots of happy comments from users ... Like Stackoverflow:)
1.选择 RDBMS 在很大程度上取决于偏好。您似乎已经倾向于 MySQL 了。没关系，因为 MySQL 便宜且流行。但是，您没有一个可以同时执行事务和全文搜索的引擎（在 MyISAM 和 InnoDB 之间）。 使用 InnoDB 进行全文搜索2.（和 4） MyISAM 与 InnoDB 和数据类型< /a>3.CREATE TABLE？我更喜欢使用数据库 IDE，例如 Toad for MySQL5.（和 6） 审查数据库规范化/PK/FK （您需要使用 InnoDB 进行 FK。）7.您忘记了索引！数据库中非常重要的因素。MySQL 中的索引是什么?
MySQL 索引 - 最佳实践是什么？
是的，如果您有上述要求，MySQL 非常适合。
但是，正如我所说，使用 MySQL/MyISAM/InnoDB，您没有可以进行全文搜索的引擎ANDLIKE 足以满足您的应用程序。
另一方面，使用 SQL Server，您可以在一个引擎中拥有所有功能，包括全文、事务和 FK。
另一种选择是使用单独的技术进行索引全文搜索。有一个 MySQL 插件：
示例：
1.Selecting a RDBMS is largely a matter of preference. You seem to be leaning towards MySQL already. That's okay, because MySQL is cheap and popular. However, you are left with not having an engine that can do transactions and full-text search at the same time (between MyISAM and InnoDB). Fulltext Search with InnoDB2.(and 4) MyISAM vs InnoDB and datatypes3.CREATE TABLE? I prefer to use a database IDE, like Toad for MySQL5.(and 6) Review of DB normalization/PKs/FKs (You'll need to use InnoDB for FKs.)7.You forgot indexes! Very important factor in a database.What is an index in MySQL?
MySQL indexes - what are the best practises?
Yes MySQL is a good fit if you have the above requirements.
However, as I said, with MySQL/MyISAM/InnoDB, you don't have an engine that can do full-text search AND transactions/FKs. A simple option is to have a 2nd copy (in MyISAM) of the InnoDB tables that need full-text search capability. You can do this because you can mix the 2 engines in the same database. Or, maybe you don't even need full-text search because
LIKEis sufficient for your application.On the other hand, with SQL Server, you can have all the features, including full-text, transactions, and FKs all in one engine.
Yet another option, is to use a separate technology for indexed full-text searches. There's a plugin for MySQL:
Example: