玩转数据库、在线开发
我正在尝试为我正在进行的一个小项目设计一个数据库。最终，我想让它成为一个网络应用程序，但现在我不介意只尝试离线数据。然而，我却陷入了十字路口。
基本概念是用户输入 10 个字段的值，与数据库中的值进行比较，每个项目都有一个加权值。我知道如果我要对其进行编码，我可以为每个字段使用查找表，将值相加，并将结果显示给最终用户。
另一个例子是必须获取两点之间的距离，每个点存储在一行中，X 值获取其自己的列以及 Y 值。
现在，如果我将数据存储在数据库中，我应该尝试在查询中执行所有操作（我认为这将涉及临时表等），还是只使用简单的查询，并操作应用程序代码中返回的行？
现在，我正在考虑选择后者（在应用程序内操作数据）并仅使用查询来减少我必须排序的数据量。你们有什么建议？
编辑：现在我正在使用 Microsoft Access 来掌握基础知识并尝试进行良好的设计。 IIRC 根据我在 Oracle 和 MySQL 方面的经验，您可以在批处理过程中一起运行命令并仅返回一个结果。但不确定是否可以使用 Access 来做到这一点。
I'm trying to design a database for a small project I'm working on. Eventually, I'd like to make it a web-app, but right now I don't mind just experimenting with data offline. However, I'm stuck in a crossroads.
The basic concept would be a user inputs values for 10 fields, to be compared against what is in the database, with each item having a weighted value. I know that if I was to code it, that I could use a look-up tables for each field, add up the values, and display the result to the end-user.
Another example would be having to get the distance between two points, each point stored in a row, with the X value getting its own column as well as the Y value.
Now, if I store data within a database, should I try to do everything within queries (which I think would involve temporary tables among other things), or just use simple queries, and manipulate the rows returned within the application code?
Right now, I'm thinking to go for the latter (manipulate data within the app) and just use queries to reduce the amount of data that I would have to go sort through. What would you guys suggest?
EDIT: Right now I'm using Microsoft Access to get the basics down pat and try to get a good design going. IIRC with my experience with Oracle and MySQL you can run commands together in a batch process and return just one result. But not sure if you can do that with Access.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您使用数据库，我强烈建议您使用 SQL 来完成所有操作。与命令式编程语言相比，SQL 对于此类工作的能力和功能要强大得多。
当然，它确实意味着您愿意将数据视为“集合”并以声明式风格进行编程。但现在花时间真正熟悉 SQL 并使用 SQL 操作数据从长远来看将会带来巨大的回报。不仅对于这个项目，而且对于未来的项目。我还建议在代码中使用存储过程而不是查询，因为存储过程提供了一个漂亮的抽象层，允许您的表设计随着时间的推移而改变，而不影响系统的其余部分。
使用和处理数据库的一个非常重要的部分是理解数据建模、规范化等。就像其他事情一样，这将是一项努力，但从长远来看，它会得到回报。
请问，当您有更好的数据库（例如 MSSQL Express）时，为什么还要使用 Access？从 MSSQL Express 到 MSSQL 或 SQL Azure 的迁移路径甚至是非常无缝的，您今天（在此项目中）所做的一切和经历的一切都将完全转化为 MSSQL Server/SQL Azure，以用于未来的项目以及该项目的增长超出您的预期。
我不明白你最后关于运行批处理过程并只得到一个结果的说法，但如果你可以在 Oracle 和 MySQL 中做到这一点，那么你也可以在 MSSQL Express 中做到这一点。
If you're using a database I would strongly suggest using SQL to do all your manipulation. SQL is far more capable and powerful for this kind of job as compared to imperative programming languages.
Of course it does imply that you're comfortable in thinking about data as "sets" and programming in a declarative style. But spending time now to get really comfortable with SQL and manipulating data using SQL will pay off big time in the long run. Not only for this project but for projects in the future. I would also suggest using stored procedures over queries in code because stored procedure provide a beautiful abstraction layer allowing your table design to change over time without impacting the rest of the system.
A very big part of using and working with databases is understanding Data modeling, normalization and the like. Like everything else it will be a effort but in the long run it will pay off.
May I ask why you're using Access when you have a far better database available to you such as MSSQL Express? The migration path from MSSQL Express to MSSQL or SQL Azure even is quite seamless and everything you do and experience today (in this project) completely translates to MSSQL Server/SQL Azure for future projects as well as if this project grows beyond your expectations.
I don't understand your last statement about running a batch process and getting just one result, but if you can do it in Oracle and MySQL then you can do it in MSSQL Express as well.
Shiv 所说的，还有……
一个好的 DBMS 包含相当多的可靠工程。有两个组件是经过特别精心设计的，即查询优化器和事务控制器。如果您认为 DBMS 只是一个愚蠢的表检索工具，那么您很可能最终会在应用程序中发明自己的优化器和事务控制器。在迁移到支持多个并发用户的环境之前，您将不需要事务控制器。
除非您的工程天赋非凡，否则您最终可能会得到一个自制的数据管理系统，它不如优秀的 DBMS 中的系统。
SQL 的学习曲线很陡峭。您需要学习如何表达连接、投影和限制多个表中的数据的查询。您需要学习如何在事务上下文中处理更新。
你需要学习简单而合理的表设计和索引设计。这包括但不限于数据标准化和数据建模。您需要一个具有良好优化器和良好事务控制的 DBMS。
学习曲线很陡峭。但从顶部看到的景色还是值得爬上去的。
What Shiv said, and also...
A good DBMS has quite a bit of solid engineering in it. There are two components that are especially carefully engineered, namely the query optimizer and the transaction controller. If you adopt the view of using the DBMS as just a stupid table retrieval tool, you will most likely end up inventing your own optimizer and transaction controller inside the application. You won't need the transaction controller until you move to an environment that supports multiple concurrent users.
Unless your engineering talents are extraordinary, you will probably end up with a home brew data management system that is not as good as the one in a good DBMS.
The learning curve for SQL is steep. You need to learn how to phrase queries that join, project, and restrict data from multiple tables. You need to learn how to handle updates in the context of a transaction.
You need to learn simple and sound table design and index design. This includes, but is not limited to, data normalization and data modeling. And you need a DBMS with a good optimizer and good transaction control.
The learning curve is steep. But the view from the top is worth the climb.