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

DB implementation

  • Phases:
    1. Definition: Target system requirements and content will be defined with customer. Specification of requirements will be the result of this phase
    2. Design: Database will be designed using specification of requirements. Entity relationship model will be the result of this phase
    3. Implementation: Implementation will be executed using entity relationship model. Application utilizing the implemented database will also be created in this phase if required
    4. Testing: Finished database will be tested outside the production environment in order to point out and fix possible bugs
    5. 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)
  • Attributes will be the columns in database table
  • Below is an example of database table

Database table example

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

DB connection example

  • 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

Creating entities from occurrences

  • 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

Entity count optimization

  • 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)

  • Below is an example of database table including all three types of fields

DB table example 2

  • 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

MySQL 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

DB design example

  • 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

DB design example 2

  • 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

DB design example 3

  • 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

DB design example 4

  • 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

DB design example 5

  • 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!