database 为购物车应用程序设计数据库？

design a database for a shopping-cart application?

提问by Quest Monger

I have never designed a database/data-model/schema from scratch, especially for a web-application. In some recent job interviews, i was asked to 'design' a database for a shopping cart application. Now i am working on a mobile shopping application (retail, uses phonegap) with a backend that needs to store and process product and order info. The scale of this problem is so huge, i don't know where to start. I was hoping for some advise on -

我从来没有从头开始设计数据库/数据模型/模式，尤其是对于 Web 应用程序。在最近的一些工作面试中，我被要求为购物车应用程序“设计”一个数据库。现在我正在开发一个移动购物应用程序（零售，使用 phonegap），后端需要存储和处理产品和订单信息。这个问题的规模如此之大，我不知道从哪里开始。我希望得到一些建议 -

1. How should I approach such a problem (shopping cart application DB) ? where should i start ?
2. Are there any common mistakes/pitfalls that i should avoid ?
3. What optimization/efficiency paradigms should i keep in mind when designing such a DB ?
4. How should i go about identifying entities in the problem space (products, orders, etc)? how should i derive the relationships between them ?
5. When a interviewer asks such a question, what exactly is he looking for ? is there something i should/should not say ?

1. 我应该如何解决这样的问题（购物车应用程序数据库）？我应该从哪里开始？
2. 有什么我应该避免的常见错误/陷阱吗？
3. 在设计这样的数据库时，我应该记住哪些优化/效率范式？
4. 我应该如何识别问题空间中的实体（产品、订单等）？我应该如何推导出它们之间的关系？
5. 当面试官问这样的问题时，他到底在找什么？有什么我应该/不应该说的吗？

I should also clarify that -

我还应该澄清——

1. Yes, I am a noob, and my motives are to learn database design AND prepare for upcoming job interviews. I have read DBMS books where they describe individual concepts in detail, but i have no clue how to put those things together and start designing a database.
2. I have seen other threads on database design. The authors already tend to posses some knowledge on how to break the problem down. i would like to understand the methodology behind doing that.
3. Links to outside resources, comments, suggestions and anything that will put me on the right track is much appreciated. I hope this thread serves as a learning experience for myself and others.

1. 是的，我是一个菜鸟，我的动机是学习数据库设计并为即将到来的工作面试做准备。我读过 DBMS 书籍，其中详细描述了各个概念，但我不知道如何将这些东西放在一起并开始设计数据库。
2. 我看过其他关于数据库设计的线程。作者已经倾向于拥有一些关于如何分解问题的知识。我想了解这样做背后的方法。
3. 非常感谢外部资源、评论、建议和任何能让我走上正轨的链接。我希望这个线程可以作为我自己和其他人的学习经验。

回答by Devjosh

There can be five tables in database:

数据库中可以有五个表：

CATEGORYthis table stores information about products categories of your store and categories hierarchy.
parent field of this table stores ID of the parent category.

CATEGORY此表存储有关您商店的产品类别和类别层次结构的信息。
该表的 parent 字段存储父类别的 ID。

PRODUCTall products of your store are stored in this table. This table has a foreign key categoryID which identifies ID of the category to which a product belongs.

PRODUCT您商店的所有产品都存储在此表中。该表有一个外键 categoryID，用于标识产品所属类别的 ID。

ORDERthis table stores information about all orders made by visitors of your store.

ORDER此表存储有关您商店访问者下的所有订单的信息。

ORDERED_SHOPPING_CARTtable is tightly connected with PRODUCT and ORDER tables; stores information on customers' orders content.

ORDERED_SHOPPING_CART表与 PRODUCT 和 ORDER 表紧密相连；存储有关客户订单内容的信息。

SPECIAL_OFFERtable contains a list of products, which are shown on home page as special offers

SPECIAL_OFFER表包含产品列表，这些产品在主页上显示为特价商品

回答by Daniel Casserly

A brief answer is the way that i would tackle this problem. Firstly, there are loads of open source or free, web based shopping carts. This means that you can get one, set up the database and then have a good look around what they did.

一个简短的答案是我解决这个问题的方式。首先，有大量开源或免费的基于网络的购物车。这意味着您可以获得一个，设置数据库，然后仔细看看他们做了什么。

Ask yourself questions such as, why have they done that? Why is it good? What downside could there be? How would i do it differently? why?

问自己一些问题，例如，他们为什么要这样做？为什么好？可能有什么缺点？我会怎么做？为什么？

I would try to procure a database design tool that allows you to visualize the database. (like database designer in visual studio or i have one from MicroOlap that does pgsql databases)

我会尝试购买一个数据库设计工具，让您可以将数据库可视化。（比如 Visual Studio 中的数据库设计师，或者我有一个来自 MicroOlap 的 pgsql 数据库）

Then you need to think about what you need in the database. What is the customer going to do? Buy products! Therefore you need a products table. Without going down the whole route you can see the point. Imagine what is needed, then basically make a table for it.

然后你需要考虑在数据库中你需要什么。客户要做什么？购买产品！因此，您需要一个产品表。无需走完整条路线，您就可以看到这一点。想象一下需要什么，然后基本上为它制作一张桌子。

If you have more than one option for a field in a table, make another table with a relation in it. So if you have a product table and you have a status field. you could have more than one status. (eg out of stock, limited number, big item, expensive) instead of hard coding these fields, make a table and allow the user to add items to the table. then in the product table add a field status_id and link it to the status table

如果表中的某个字段有多个选项，请创建另一个包含关系的表。因此，如果您有一个产品表并且有一个状态字段。您可以拥有多个状态。（例如，缺货、数量有限、商品大、价格昂贵），不要对这些字段进行硬编码，而是制作一个表格并允许用户将商品添加到表格中。然后在产品表中添加一个字段 status_id 并将其链接到状态表

Many - many relationships are useful things to know. (i fell short to this myself.) say you have a component and product tables. The products can be made up of lots of components and the components could be allocated to many products. Create a mediator table. Something like prodcomp( and in this you would have fields like id, prod_id, comp_id, qtyneeded).

许多 - 许多关系是需要了解的有用信息。（我自己没有做到这一点。）说你有一个组件和产品表。产品可以由许多组件组成，并且组件可以分配给许多产品。创建一个中介表。类似于 prodcomp( 并且在此您将具有诸如 id、prod_id、comp_id、qtyneeded 之类的字段)。

Learn to index correctly.

学会正确索引。

Don't create the database until you have a solid idea of how it will work. this saves time in recreating it later.

除非您对数据库的工作原理有明确的了解，否则不要创建数据库。这可以节省以后重新创建它的时间。

There may be more to this, however, i hope i have given you a good start.

可能还有更多内容，但是，我希望我已经给了你一个好的开始。