Database design¶
- Database design phase has a lot of common with designing process in software development
- Design phase is especially important in larger database projects
- One of the essential goals in this course is that student will be able to design a database
Goals and phases in database design¶
-
Database design has the following benefits:
- General purpose: Same structure applies to different environments and for different customers
- Integrity: Avoid the duplication of data and verify the validity of data
- Coverage: All necessary information should be stored into the database
- Easing the programming: Well designed database speeds up the implementation phase
- Simplicity: Avoid complex database structure
- Performance: Meet the required time delays in data retrieval and manipulation
-
The phases in database design can be covered with the illustration below
- Phases:
- Definition: Target system requirements and content will be defined with customer. Specification of requirements will be the result of this phase
- Design: Database will be designed using specification of requirements. Entity relationship model will be the result of this phase
- Implementation: Implementation will be executed using entity relationship model. Application utilizing the implemented database will also be created in this phase if required
- Testing: Finished database will be tested outside the production environment in order to point out and fix possible bugs
- Deployment and maintenance: Finished system will be deployed into the production environment
Terms - Entities and attributes¶
- Entity is a group of similar items (for example person, student and car)
- Entities will be implemented as tables in the database
- Attributes and attribute values describe entity properties
- Below are some examples of attributes
- Unambiguous or ambiguous
- For example personal identification number (unambiguous) or degree (ambiguous)
- Permanent or variable
- For example personal identification number (permanent) or employer (variable)
- Required or optional
- For example first name (required) or email address (optional)
- Unambiguous or ambiguous
- Attributes will be the columns in database table
- Below is an example of database table
Terms - Connection between entities¶
- Entities can be connected through couple of different connections
- In entity relationship model, entities and connection between entities can be named to further clarify the model
- Below is an example of one-to-many connection between two entities
- Usually verb can be used for naming the entity connection
- This verb can be used for naming the join table between these entities in case of many-to-many connection
- When defining the type of connection between two entities, connection and entities should be read aloud like in the example below
-
Example:
- Many persons play in team
- Customer creates many orders
- There are many participants in the event
-
These sentences must be read also other way around:
- Can person play in many teams?
- Can one order belong to many customers?
- Can person participate to many events?
Database modeling phases¶
Phase 1 - Gather entities¶
- In the first phase specification of requirements should be read and gather all possible terms
- From the list of terms, entities and attributes should be distinguished
- Connections between entities are created based on occurrences
- Example of this is presented below
- As can be seen from the example above, John and Christine both have swimming as a common hobby
- John also has tennis as his hobby
- Here we have two entities: Person and Hobby
- The connection will be created from the following two sentences:
- Can one person have many hobbies? → Yes
- Can many persons have same hobby? → Yes
-
This means that many-to-many connection will be formed between these two entities
-
When all necessary entities have been gathered, it would be a good idea to think whether the amount of entities can be minimized
- This way the overall structure of database can be optimized
- Below is an example where three different entities are combined into one entity
- As can be seen from the example above, Movie, Game and Music entities were included as Type attribute values in Entertainment entity
Phase 2 - Entity attributes¶
- In this phase entity attributes are gathered
- Attributes will be used as columns in database table
-
Attributes can be any of the following type:
- Primary key
- Required (identifies rows)
- Can be consisted of two or more attributes (Composite key)
- Cannot be empty (NULL)!
- Usually presented by underlined, bolded or letters PK in entity relationship model
- Foreign key
- Can be part of composite key
- Usually presented with italic font or letters FK
- Other information (Non-key attributes)
- Primary key
-
Below is an example of database table including all three types of fields
- In addition for giving attribute a name, the actual column in database table also requires a data type to be defined
- Column data type describes what kind of data will be stored into column fields (for example numeric or string)
- Data type also represents how much space stored data will take and whether the data will be fixed-length or variable length
- Below is a list of some of most commonly used data types
Source: https://www.mysqltutorial.org/
Phase 3 - Relationships between entities¶
- When entities and attributes have been defined relationships will be formed between entities
- You'll have to think, just like in the real world, how do entities relate to each other
- There are three types of relationships:
- One-to-one: Record in one table is associated with only one record in another table
- One-to-many: Record in one table can be associated with one or more records in another table
- Many-to-many: Multiple records in one table are associated with multiple records in another table
- Below is an example where employee is connected to team
- As can be seen from the example above, there is a one-to-many connection between two entities
- Team can have multiple employees as members and each employee belongs to one team.
- Here we can add the conditionality, which shows whether the key value (Team_ID in this case) is required in Employee table or not
- Conditionality can be described like this:
- 'ball' → Field value is optional
- 'line' → Field value is required
- Below is the same example as shown previously but with added conditionality
- Now we can read this connection again like this:
- Team can have multiple employees as members and each employee MUST belong to one team.
- In other words, when field is required, there will be a NOT NULL definition in database table for this field
-
When adding a new line to the table, field with NOT NULL definition cannot be left empty
-
Many-to-many relationships are not possible in databases and must be solved
- This relationship will be solved by inserting a new entity between this kind of connection
- As a result there will be two one-to-many or one-to-one relationships
- Below is an example where there is a many-to-many relationship between customer and product entities
- In order to create a new entity between two presented entities, think this through a sentence like this:
- Customer orders a product.
- Now there is a verb called order and this can be utilized while naming a new entity
- Below is an example where many-to-many relationship has been broken down with new entity called Order
- Now let's read these two new connections again:
- Customer can have many orders, but each order must belong to one customer.
- Product can belong to many orders and each order can have many products.
- This means that there is still many-to-many connection between Order and Product entities
- Let's break this down by creating an entity called Products_in_order table between these two entities
- Now there are no many-to-many relationships left
- In addition, Date and Coupon attributes have been added to Order entity and Quantity attribute have been added to Products_in_order entity
- New attribute descriptions:
- Date refers to a date when order has been made and Coupon presents the possible discount code given during the order
- Quantity tells how many products of particular type have been added to the order
- All these new attributes are entity specific and usually add valuable information which cannot be retrieved from parent entities Customer and Product
Phase 4 - Entity definitions¶
- Entity definition means table comment in database
- Definitions are recommended since they usually give a better picture for a third person viewing the database design
- Definitions can be short and even a short definitions usually gives a better understanding than just looking at table name and fields included
- Below are two examples of a comment for database table:
- Employee table: Company employees from all teams are stored in this table.
- Team table: Company teams are stored in this table.
- Important: Well designed database is self-explanatory and only by looking its entity relationship model should give a good understanding of the structure and the meaning of fields in most cases!