SQL 需要汽车公司的 ER 图帮助

Need help on an ER Diagram for an automobile company

提问by kdenz

I'm working on a small uni database project, I would like to know if my ER design is good enough for me to move on to further steps.

我正在处理一个小型的 uni 数据库项目，我想知道我的 ER 设计是否足以让我继续执行进一步的步骤。

Further steps involve: Translating ER to Relational diagram, and basically implement it as a database for a database application, in which user can search and browse stuff through an interface.

进一步的步骤包括： 将 ER 转换为关系图，并将其基本实现为数据库应用程序的数据库，用户可以在其中通过界面搜索和浏览内容。

Here's the project description:

这是项目描述：

The application is an automobile company, such as General Motors, Ford, Toyota, or Volkswagen (or maybe a company from yesteryear like Studebaker, Hudson, Nash, or Packard). In our hypothetical company, it has been decided to redesign a major part of the database that underlies company operations. Unfortunately, the manager assigned to solicit database design proposals is not very computer literate and is unable to provide a very detailed specification at the technical level. Fortunately, you are able to do that. The company needs to keep quite a bit of data, but we shall focus on the following aspects of corporate operations.

应用程序是一家汽车公司，例如通用汽车、福特、丰田或大众（或者可能是过去的公司，例如 Studebaker、Hudson、Nash 或 Packard）。在我们假设的公司中，已决定重新设计作为公司运营基础的数据库的主要部分。不幸的是，被指派征求数据库设计建议的经理不太懂计算机，无法在技术层面提供非常详细的规范。幸运的是，您能够做到这一点。公司需要保留相当多的数据，但我们将重点关注公司运营的以下几个方面。

• Vehicles: each vehicle as a vehicle identification number (VIN). Lots of stuff is encoded in real VINs (they are well described on Wikipedia), but you can just make them up if you want.
• Brands: each company may have several brands (for example, GM has Chevrolet, Pontiac, Buick, Cadillac, GMC, Saturn, Hummer, Saab, Daewoo, Holden, Vauxhall, and Opel and Volkswagen has Volkswagen, Audi, Lamborghini, Bentley, Bugatti, Skoda, and SEAT)
• Models: each brand offers several models (for example, Buick's models are the Enclave, LaCrosse, and Lucerne, and Mercury's models are the Mariner, Milan, Sable, and Grand Marquis). Each model may come in a variety of body styles (4-door, wagon, etc.)
• Options: we'll stick to color, and maybe engine and transmission.
• Dealers and customers: dealers buy vehicles from the manufacturer and sell them to customers. We'll keep track of sales by date, brand, model, and color; and also by dealer. Note that a dealer may not sell any of the car company's brands. Dealer's keep some cars in inventory. Some, of course, are already sold, but the dealer still keeps track of that fact.
• Suppliers: suppliers supply certain parts for certain models.
• Company-owned manufacturing plants: some plants supply certain parts for certain models; others do final assembly of actual cars.
• Customers: in reality, lots of demographic data are gathered. We'll stick to name, address, phone, gender, and annual income for individual buyers. The customer may also be a company (e.g. Hertz, Avis, or other companies that maintain corporate fleets, but we'll skip that).
• We'll skip data on corporate finance, pending bailouts, bankruptcy status, etc. Not that these data are unimportant, but we need to keep the project within bounds.

• 车辆：每辆车作为一个车辆识别号（VIN）。很多东西都是用真实的 VIN 编码的（它们在 Wikipedia 上有很好的描述），但如果你愿意，你可以随意制作它们。
• 品牌：每家公司可能有多个品牌（例如通用有雪佛兰、庞蒂亚克、别克、凯迪拉克、GMC、土星、悍马、萨博、大宇、霍顿、沃克斯豪尔和欧宝，大众有大众、奥迪、兰博基尼、宾利、布加迪、斯柯达和西雅特）
• 车型：每个品牌提供多种车型（例如别克的车型是英克雷、君越、卢塞恩，水星的车型是水手、米兰、紫貂和大侯爵）。每个型号可能有多种车身样式（4 门、旅行车等）
• 选项：我们会坚持颜色，也许引擎和变速箱。
• 经销商和客户：经销商从制造商处购买车辆并将其出售给客户。我们将按日期、品牌、型号和颜色跟踪销售情况；也由经销商。请注意，经销商不得销售汽车公司的任何品牌。经销商在库存中保留一些汽车。当然，有些已经售出，但经销商仍在跟踪这一事实。
• 供应商：供应商为某些型号提供某些零件。
• 公司自有制造工厂：一些工厂为某些型号供应某些零件；其他人进行实际汽车的最终组装。
• 客户：实际上，收集了大量人口统计数据。我们将坚持个人买家的姓名、地址、电话、性别和年收入。客户也可能是一家公司（例如 Hertz、Avis 或其他维护公司车队的公司，但我们将略过）。
• 我们将跳过有关公司财务、未决救助、破产状态等的数据。并不是说这些数据不重要，但我们需要将项目控制在范围内。

Here's the ER diagramI came up with:

这是我想出的ER图：

采纳答案by Neil McGuigan

I worked on a multi-tenant car dealership database for a couple of years.

我在一个多租户汽车经销商数据库上工作了几年。

Some things to consider:

需要考虑的一些事项：

1. You need to differentiate between Products and Assets. The product is the thing you sell (just the specification of a car, with a model number), and the Asset is the thing the customer drives away in (it has a VIN).

2. You should consider the Party Model as you might sell to employees, buy from Customers, etc.

3. How to deal with trade-ins? They are probably best seen as an adjustment on a sales order.

4. How to sell goods, services, financial instruments (warranties) on the same sales order? You need abstraction here.

1. 您需要区分产品和资产。产品是您销售的东西（只是汽车的规格，带有型号），资产是客户开走的东西（它有一个 VIN）。

2. 您应该考虑派对模型，因为您可能会向员工出售、从客户那里购买等。

3. 如何处理以旧换新？它们可能最好被视为对销售订单的调整。

4. 如何在同一销售订单上销售商品、服务、金融工具（保修）？你在这里需要抽象。