Introduction
A large amount of small to medium size design companies, including the one my parents runs and the many ones I’ve worked for or worked with, do not have a well organized database that stores their data. Jan Harrington once asked on the first page of his book Relational Database Design and Implementation whether any modern business doesn’t have a digitized database. “It is hard,” he claimed (Harrington 2016, pp.3). Yes. Many design companies, at far as those I know, do have a somewhat data collection stored in their “servers”. However, not many of them have a database that really have been designed. Therefore, problems occurs – historical blueprints and files are hard to find, product information from suppliers spread in dozens of computers, contributors to old projects are unclear, each stage of requirements from clients are sometimes lost track and result in unsatisfactory service, each employee has their own collection of “inspirational images” “material maps (for rendering)” stocks and has no channel to share, etc. With the purpose of exploring a way to solve these problems and to find the a good model of design companies’ database, I chose this topic for my Database Design and Development course (instructed by Dr. Monica Maceli). A hypothetical interior design firm with 30-50 employees has been considered as the client, taking my parents’ company as a template, and a relational database base on the company’s need is created.
Conceptual and Logical Design of the database
The design was taken place following the three stages Harrington (2016) proposed. Conceptual Level, Logical Level, and Implement Level. In the conceptual level stage, business rules were extrated from the iterviews I held with the stakeholders and the potential users that has the similar needs (i.e. stakeholders, managers, and designers from my parents’ company), which refelects the realistic database needs from an interior design company.
Business Rules:
1. Each client assigns one or more contact persons; Each contact person is assigned by one client.
2. Each contact person contacts many projects; Each project might be contacted by more than one contact person.
3. Each project has one contract; Each contract is accomplished by one project.
4. Each contract is fulfilled by one or more payments; One or more payments fulfill a contract.
5. Each project contains many stages; Many stages are contained in one project.
6. Each stage produces one or more portfolios; One or more portfolios are produced in one stage.
7. Each portfolio contains many drawings; Many drawings are contained in one portfolio.
8. Each portfolio contains many documents; Many documents are contained in one portfolio.
9. Each drawing is created by one or more employees; Each employee creates many drawings.
10. Each document is created by one or more employees; Each employee creates many drawings.
11. Each drawing is reviewed by one or more employees; Each employee reviews many drawings.
12. Each document is reviewed by one or more employees; Each employee reviews many drawings.
13. One project might be supported by one or more product lists; Each product list supports one project.
14. Each product list includes one or more products; Each product contains in one or more product lists.
15. Each product is provided by only one supplier; Each supplier provides one or more products.
Conceptual Model
Beyond this, a conceptual model that depicts the relationship among the entities has been created base on the business rules. 13 entities has been draw out from the business rules and their relationship and relationship types has been depicted with a diagram, using Crow’s Foot Notation.
Entity Relationship Diagram
Futhermore, each entity has been polished in-depth, the attributes within each entity, constraints, primary keys (PK) and foreign keys (FK) and et cetera has been added to the design.
Since this is a semi-real project, I’ve been asking questions to the real users consistently. Although the main structure didn’t change much, the details has varied a lot from the original proposal. One most important update is that the firm doesn’t really have a “budget” for each project. Compare with manufacturing businesses, the main cost of a design company is human resources and time. Therefore, the budget entity has never been added into the conceptual model. Another key change is the removal of “design stock”. Although necessary, but it will complicates the database significantly. More importantly, the stakeholder is more interested in utilizing this database as a management tool instead of a design assistant.
On the other hand, when unpacking the concept model to the logical level, several optimization has been taken. Firstly, the DOCUMENT entity and DRAWING entity has been merged, for the reason of their similar data structure and exact same relationships to other entities. Therefore, these two entities has been combined as FILE entity and using the attribute FILE_TYPE to distinguish them. In addition, between EMPLOYEE and FILE, instead of a simple M:N relationship, a binary relationship has being used to differentiate reviewers and authors. It was base on a later interview to one design team leader, that not just author of a drawing, the reviewer is equally important, and sometimes the reviewer could also be one of the authors. Therefore, it would be reasonable to separate these two relationships.
Based on these changes, an entity relationship diagram (ERD) has been developed.
Database Implementation
Using MySQL, the database has been implemented on a local drive. The scripts that being used to create this database can be viewed via my GitHub repo.
Three major changes has been made during the implementation stage. The first is that I removed TOTAL_PRICE attribute from the PRODUCT_LIST entity, for the reason that it would be hard to maintain accurate when it’s manually inserted. Alternatively, I decided to create a View to help end-users to showcase the total price as a derived attribute. (see Task 3) This approach will assure the queried result maintains accurate, even after new PRODUCTs has been added to the PRODUCT_LIST like it has been done in Task 4. Another major change is a new entity ROLE has been added to the database, directly connect to the ASSIGNMENT entity, and replaced the original ENUM ROLE attribute. This change would allow end users add new types of ROLES, instead of stuck with the limitation of ENUM listed entries. Similarly, a FILE_TYPE entity has been added and connect to the FILE entity directly. The last major change is that I removed the DEPARTMENT entity. The company (HLD) actually doesn’t have a department like structure, and the reason I created it in the design stage was that they have the plan to add such structure. However, considering the scope of this project, it would be less important than other entities.
There are also many minor changes as well, some are renaming of entities/attributes. For instance, the STAGE entity has been renamed as PHASE, for that I found its’s a more appropriate translation. On the other hand, some attributes has been added during the data-entry process. Although these are just fake data, they actually helped me understand the database more and force me think through it. One worth mentioning one is the CURRENT_WAGE attribute that being added to the ASSIGNMENT entity. It is because, when I was entering the data, I realized that the employee’s position and salary could change (i.e. task 8). This would lead to a result that in the future, after some employee’s salary has bumped-up, it won’t be able for the company managers to accurately calculate historical projects’ human resource costs. Therefore, I decide to add this attribute to capture the timestamp of the employee’s wage level when working on the very project.
Lastly, one worth mentioning design details of the database is the enforcement of one to one (1:1) relationship. As a common relationship among the concept of entity relationship datamodels, 1:1 relationship is surprisingly not naturally supported by MySQL. One work-around that I have used is to set the FK PROJECT_ID in CONTRACT entity with a UNIQUE constraint, which basically enforced each CONTRACT can correlated with only one PROJECT.
Sample Queries
In order to meet the potential needs of the hypothetical client, several sample tasks have been raised, and quries that solve the tasks has been written to test the validity of the database.
Yiren Wang 