Relational Database Design For Beginners (Part 1)

This time

  • Major Concepts
  • Types and Benefits
  • Data Modeling and Design Process

Well, for the last 3 days, I’ve been studying relational database design from scratch as a fresher front-end developer, whom has not ever been studying or working with it. So, this article is the full version containing every important things for me at the current time. Not colorful, not ornate, but maybe useful, so let’s get started…

Major Concepts

Definition: A database simply is…

  • Something to store data on a computer
  • The stored data then can se searched and retrieved later
  • The databases are usually stored on large computers called servers
  • We can create a database on our own computers

Where are databases used?

  • Websites
  • Desktop applications
  • Phone applications
  • Internal systems used by companies: utilities, insurances…

Databases compositions

  • The major child of every databases are ‘tables’: table === an object used to store data
  • A database can contains several tables, and the number of tables depends on the size and complexity of the application

Database alternatives

  • Flat files: an easy-to-search, not-easy-to-update text file for storing data, and also not able to be scaled well
  • Spreadsheets: excel or googlesheet files, not optimised for access by applications and also does not scale well

TYPES AND BENEFITS

We have 2 main types of databases

  • Relational database
    • Represents related sets of data
    • Linked using relationships
    • Designed for easy update, addition, and removal of data
    • Main type of database used in websites and applications
  • Data warehouse
    • Based on a large central table with othe linked tables
    • Fewer number of tables, but more records on each
    • Usually used for reporting
    • Data warehouses are great, but they are not as optimized as relational databases for adding to or updating

Benefits of relational databases

  • Data are stored in seperate components, which are called ‘tables’
  • Remove duplicate information
  • Ensure records are complete
  • Naming consistency
  • Combine data sets easily
  • Increase security

DATA MODELING - DESIGN PROCESS

What is data modeling - data model

  • Data modeling is…
    • A process of analysing data requirements and identifying the objects to be used for a database
    • After data modeling, a data model is created
  • Data model types: conceptual → logical → physical

More about the data model types

  • Conceptual data models: a high-level model showing the relationships between objects, but usually just contains the names or concepts (such as university, teacher, student, subject…) (desktop or horizontal mobile for correct displaying)
  -----------------      -----------------
  |  teacher      |      |  class        |
  -----------------      -----------------
  • Logical data model: shows objects at a more detailed level, including information for each object (desktop or horizontal mobile for correct displaying)
  -----------------     -----------------
  |  teacher      |     |  class        |
  -----------------     -----------------
  |  teacher_id   | --- |  class_id     |
  |  teacher_name |     |  class_name   |
  -----------------     |  class_time   |
                        -----------------
  • Physical data model: describe the internal schema of the database, including table names + field names + keys + relationships (desktop or horizontal mobile for correct displaying)
  ----------------------------     ----------------------------
  |  teacher                 |     |  school_class            |
  ----------------------------     ----------------------------
  |  teacher_id INT(10)      |     |  class_id INT(10)        |
  |  first_name VARCHAR(200) | --> |  class_name VARCHAR(200) |
  |  last_name VARCHAR(200)  |     |  start_time DATETIME     |
  |  active_status INT(1)    |     |  end_time DATETIME       |
  ----------------------------     ----------------------------

And the design process…

  • Determine the goal of the database (*)
  • Consider the current system
  • Gathering database requirements
  • Identifying entities and attributes (*)
  • Follow the 1NF, 2NF and 3NF rules (*)
  • Design considerations

The steps with icon ‘(*)’ at the ending are the most important ones, and next time we’ll dig deeper into them.

Next time:

  • Deeper into major steps of design process
  • ‘Normalization’ and the 3 Normal Forms