I have a task to design an ER diagram and task is given as points below is the description of the problem:
- A telecommunications company employs thousands of consultants.
- Each consultant is assigned to a company department, each department might be a part higher level company division up to the CEO board (multiple levels)
- The personal data for each employee include:first name, last name, address, phone number and email
- The company provides different types of services –online sales, advertising, maintenance, each employee has a specific primary and optionally secondary role assigned
- The company collects information about clients including their personal data and also all signed contracts, sold devices and active premium services
- Each contact with a customer is registered in dedicated service ticket including type of service (contract, issue, advertising), start and end date and customer id
- The ticket tracks a progress of particular case by recording each activity performed by the employee using a specific type of activity, date, and arbitrarily long description
- Each ticket has a status flag (e.g. registered, in progress) and for closed tickets the closure summary is added.
I designed an ER diagram and it is below my question. Is it a good idea to design point 8 where
for closed tickets the closure summary is added. part via an optional summary table.
Looking for your advice. Also I am not sure if this is a good Database design I know it is a time taking task, but I really appreciate if someone can tell me "How good" my design is, and whether it requires any change. Thanks!