Need help on an ER Diagram for an automobile company

Question

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.

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

Here's the ER diagram I came up with:

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.

Comments

[Cristian Ciupitu]

Your question is a bit too broad. What are the further steps? Edit your question and mention some of those steps. Also instead of putting screenshots of the description, you should put the text directly.

[kdenz]

@CristianCiupitu Right, thanks!

[kdenz]

@Cool! Thanks for the advice!