Question

Suppose you are the director of the sales department of Easy Toys Ltd. You need a database application to record your new business leads. Your requirements are:

1 The identity of the staff who is developing the new business lead, including the staff’s first name, last name, and employee number.

2 The identity of the potential client, including the company name, key contact person, and the company address.

3 Information about each new business lead, including the type of product classifications (e.g. STEM / dolls / electronic toys / cars).

4 Information about each contact made to develop the new business lead, including the date of the contact, how much time the staff has spent on the contact, and a brief summary of the points discussed.

The IT department of Easy Toy Ltd tells you that they already have a lot of backlog and they cannot handle your request. You decide to build the system with one of your sales managers. Your sales manager proposes the following relational database design:

Table name	Primary key	Non-key attributes
Business lead	Employee name	First name, Last name, Company name, Key contact person, New product classification, Date of contact, Time spent, Brief summary

Required:

a) What are the problems in the above relational database design proposed by the sales manager?

b) Recommend a relational database design that can eliminate all the problems identified in part (a)

Answer 1

a. The problems faced in the relational database design proposed by sales manager are:

1. All employees will try to get more than 1 lead to achieve their targets. Hence, Employee name cannot be used as the Primary Key, as primary key must contain a unique value for all the rows in the table, and it should not be repeated.

2. Even if we consider every employee targets only one company, it doesn't make sense to have the Employee's name as the Primary key, as the purpose of the table is to track the business lead and not the employee.

3. Company Contact Number and Contact Address which are crucial for following up on the conversation has not been added to the attributes of the table.

4. As a director, I would be more interested to track the status of the deal and filter based on the same. That attribute is not included in the above table.

b. Recommendations for a potential Relational Database Design:

Assumptions:

1. The database is populated for a small or medium business setup.

2. A Company is contacted only once for a deal.

Relational Database

Table Name	Primary Key	Non-key Attributes
Business Leads	Company Name	Key Contact Person, Contact Number, Contact Address, New Product Classification, Employee First Name, Employee Last Name, Employee Number, Date of Contact, Status, Time Spent, Brief Summary

Considering the above mentioned assumptions, we have plotted the above RDB, with Primary key as the Company Name, as I would be interested to retrieve the data based on the company that I am targetting.

Assumptions:

1. The database is populated for a large scale business.

2. Companies that are dealt with are also big scale and hence, more than 1 deal is made (maybe for more than 1 classification)

Table Name	Primary Key	Non-key Attributes
Business Leads	Business Lead Number	Company Name, Company Id Number, Key Contact Person, Contact Number, Contact Address, New Product Classification, Employee First Name, Employee Last Name, Employee Number, Date of Contact, Status, Time Spent, Brief Summary.

In this case, we will have millions of records and hence, it is not ideal to have string as a primary key. Also, the Company will be repeated more than once. Also, to easily filter based on companies, we will have a Company Id Number, so that entries are not missed to be filter if many salespeople store the company name differently. For example: Amazon / Amazon Development Centre, etc.

Based on the size of the company and the key requirements, we can modify and use the RDB as per convenience.