sqldatabaserelational-databaseentity-relationship

Need help on an ER Diagram for an automobile company


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.

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.

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.

Here's the ER diagram I came up with: ER diagram


Solution

  • 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.