小型电脑店的数据库设计，寻求建议
作为学习一些数据库设计技术的练习，我正在为一家虚构的小型计算机商店设计一个数据库。这个想法是，商店从多个供应商那里获取一些组件，将它们组装成计算机系统，然后出售给客户。
这是我迄今为止提出的模型的图像： 图片
一些解释：
该模型的中心实体是一个 Item。它代表商店拥有或已出售的单个商品，例如一个精确的 i7 920 处理器。该商品属于一个产品（在本例中为“Intel i7 920”），该产品属于一个类别（“处理器”）。
这些物品通过订单进入商店。当从供应商处订购组件时，会在订单表中创建一个新条目。对于订购的每个项目，都会在 Items 表中创建一个新条目，并在 OrderItem 中创建相应的条目（我无法为该表想出更好的名称...）。
订单项条目基本上包含商店从供应商处收到的发票上的内容：商品的价格、相同的描述等。如果必须显示或打印订单，则订单字段确定商品出现的顺序。
另一方面，当客户购买商品时，就会创建发票。他们购买的每个项目都会创建一个发票项目。它的工作方式基本上与 OrderItem 相同。价格字段是客户将支付的价格（假设价格不是每个产品的价格，而是为每个发票定制的价格。我找不到一种优雅的方法来获取每个产品的价格并能够跟踪价格变化） 。如果描述为 NULL，则相应产品的描述将显示在发票上，否则，可以输入该发票的自定义描述。
现在棘手的部分是组件表。如果商店销售名为“PC 1”的 PC 系统，则产品中将会有“PC 1”条目，例如属于“PC”类别。每次组装“PC 1”系统时，都会创建一个新项目（产品 ID 为“PC 1”），并且对于该系统的每个组件，都会向组件中添加一个条目： assemblyID 将是新项目的 itemID , componentID 是组件的 itemID。
我只是想到了另一种在没有组件表的情况下管理此问题的方法：商店可以以 0 美元（实际上是 0 瑞士法郎）的价格“出售”组件给虚构的客户，然后从虚构的供应商处“购买”组装好的计算机（同样，对于自由的）。这似乎更容易做到，因为组件将从库存中消失，但是是否有一种简单的方法将“出售”的组件链接到“购买”的计算机？
现在，我对从中学到的东西感到非常满意。但肯定有一些错误或一些更好的方式来表示我想不到的东西。我很高兴收到任何建议和批评。提前致谢！
PS：抱歉，如果文本有点长......另外，对于某些英语不好的情况感到抱歉（这不是我的主要语言（这也解释了一些选择不当的字段/表名称（对此我非常乐意得到）建议）））
As an exercise for learning some database design techniques, I'm designing a database for a fictional small computer shop. The idea is that the shop gets some components from multiple suppliers, assembles them into computer systems and sells them to customers.
Here's an image of the model I've come up with so far:
Image
Some explanations:
The central entity of this model is an Item. It represents a single item the shop owns or has sold, for instance one precise i7 920 processor. This Item belongs to a Product (in this case "Intel i7 920"), which belongs to a Category ("Processors").
These Items enter the shop via an Order. When components are ordered from a Supplier, a new entry in the Orders table is created. For each Item that is ordered, a new entry in the Items table is created, with its corresponding entry in OrderItem (I couldn't come up with a better name for this table...).
An OrderItem entry basically contains what the shop received on the invoice from the supplier: the price that item cost, the same description, etc. The order field determines in which order the items appear if the Order must be displayed or printed.
On the other side, when a Customers buys something, an Invoice is created. Each Item that they buy, an InvoiceItem is created. It basically works the same way as OrderItem. The price field is the price the Customer will pay (let's assume the prices are not per-product, but customized for each Invoice. I couldn't find an elegant way to have per-product price and be able to track the price changes). If description is NULL, the description of the corresponding Product appears on the Invoice, else, a custom description for that Invoice can be entered.
Now the tricky part is the Assemblies table. If the shop sells a PC system called "PC 1", there will be a "PC 1" entry in Products, belonging to the "PCs" category for example. Every time a "PC 1" system is assembled, a new Item is created (with the productID of "PC 1") and for each component of this system, an entry is added to Assemblies: assemblyID will be the itemID of the new Item, componentID is the itemID of the component.
I just thought of another way of managing this without the Assemblies table: the shop could "sell" the components for 0 $ (well actually 0 CHF) to a fictional Customer and "buy" the assembled computer from a fictional supplier (again, for free). This seems easier to do, as the components would disappear from the stock, but would there be an easy way to link the "sold" components to the the "bought" computer?
Now, I'm pretty happy with what I've learned form that. But there are certainly some errors or some better ways to represent things I couldn't think of. I'm happy to receive any suggestions and criticism. Thanks in advance!
PS: Sorry if the text's a bit long... Also, sorry for some cases of bad English (it's not my main language (that would also explain some poorly choses field/table names (for which I would be more than happy to get suggestions)))
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您所描述的从各个部件创建 PC 的过程是物料清单，这里是一个 网站< /a> 有很多关于数据库的信息，他们实际上有数据模型示例，包括物料清单。
The process you are describing in the creation of a PC from individual parts is a Bill of Materials, here is a website with a lot of information about databases and they actually have data model examples, including bill of materials.
您此时正在进行的过程称为数据库规范化，即在查询处理时间方面使数据库可扩展且更快的行为。
我立即可以看到 Customers 表中的 Remarks 应该有自己的表，称为 Remarks，该表通过 CustomerID 与您的 Customer 表绑定。这样客户就可以无限发表评论。
与发票相同，您可能还需要设置一个 Payments 表来控制所有客户进行的所有付款，分别将这两个表与 PaymentID 和 InvoiceID 绑定在一起。
希望这有帮助。
The process you are working on at this time is called Database Normalization, the act of making database scalable and faster when it comes to query processing times.
Right off the bat I can see Remarks in the Customers table should be given its own table called Remarks which gets tied to your Customer table via the CustomerID. This way the customer can leave unlimited remarks.
Same thing with Invoices, you might also want to setup a Payments table to control all the Payments made by all customers, tying both these tables together with PaymentIDs and InvoiceIDs respectively.
Hope this helps.