I am completely new to database design, and have been given the following scenario. I have spent hours trying to work out how to draw an ERD for this scenario but am no closer to working it out. Does anyone have any advice for where i could start? I think this is a bit difficult for a complete beginner?
Scenario: Car Sales/Service Group
.
Details of sales, marketing, parts and service staff sufficient to identify each individual, their role and primary dealership should be maintained in the database – note that there is a separate HR database that this system will not replace.
Each dealership has a General Manager in addition to a manager responsible for the departments within that dealership.
A dealership may market, sell and service more than one brand of vehicle, but not normally more than two brands – although all brands are available through the head office dealership.
A brand is managed across the group by a Brand Manager, based at Head Office. Dealership General Managers report to their respective Brand Manager. Where a dealership is affiliated to more than one Brand the General Manager has two reporting chains.
Each dealership also has a Marketing Manager who is responsible to the Group Marketing Director and the respective Brand Manager(s) for all marketing activities. Marketing Managers organise and run a busy calendar dealership-level of marketing activities, promotions and events for current and prospective customers.
The database should support the work of the marketing team by tracking the marketing events, the customers invited to them, those that attend, and those that don’t.
Customers (prospective and current) should only receive marketing materials about the brand(s) they have either expressed an interest in, or have previously purchased or had serviced at dealership locations from one dealership per brand.
A Customer’s primary dealership is the dealership that they have either purchased a vehicle through or, if a prospective customer, have initially visited. A customer who has not purchased a vehicle from the group but have had a vehicle serviced will have the servicing dealership as their primary.
The database should manage and record the service history for each car sold or serviced in one of the dealership’s service garages. Note that not every dealership has a service garage but service garages are only located at dealerships – with the exception of the Specialist Service Centre which is located in its own facility.
The service department want to be able to run a weekly process to determine which customers have an annual service due in the next month in order to ensure marketing send a letter inviting customers to book their vehicle in for a service.
The parts department want to be able to monitor the stock levels of all parts held by the group and their location. Whilst most parts will be at the central parts depot (co-located with the Specialist Service Centre) individual dealerships hold standard service parts and customer special orders. It is important that any member of the group’s parts team (in head office, central depot or in a dealership) is able to determine the holdings for a particular part and their location. Users should also be able to identify parts that have been ordered for a specific customer.
The database should track a customer’s details and their past purchase and service history with the group.
As the dealership deals in high-end and performance marques the database applications will only allow dealership General, Marketing and Sales Managers in addition to all Head Office staff to access the details of VIP and VVIP customers. The database is therefore required to be designed to be able to facilitate this functionality.
