It’s common knowledge that the best way to learn something is to practice it in a real-life scenario. Cleanmyphone 3 6 3. Obviously, the same applies to database modeling. Therefore, in this article I decided to teach you how to create a simple database structure, taking a textbook example of a hotel room reservation system. I will show you how to get started and give you some ideas for extending the model.
- Records 1 5 8 – Innovative Personal Database Training Courses
- Records 1 5 8 – Innovative Personal Database Training Programs
- Records 1 5 8 – Innovative Personal Database Training Reliaslearning
Database Modeling: Discover, Discover, Discover
In this article we will design a data model for a hotel room reservation system. We look for a data model where we can represent information about the rooms, the guests and the reservations booked at our imaginary VERTABELO***** Hotel. All this information will be stored in tables.
Database, any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval, modification, and deletion of data in conjunction with various data-processing operations. Criminal justice training programs: records management, NIBRS, public records, case management and more. Training Program. Module 1: Introduction to records management – THIS SESSION. Learning Objective: Participants will learn the basics of records management, the records management policies/procedures at Ontario Tech and how to use the RCRS to classify records. Followp task: -u. Complete a questionnaire on your unit’s record storage.
Database modeling is a cyclic discovery process. We first identify the main tables and its attributes. In our model, the main tables are:
room
, guest
and reservation
. Then we continue refining our tables by discovering their attributes or columns. For example, the room
table has attributes like: room number
, name
and smoke
flag among others.Reservation
table has attributes date_in
, date_out
, status
(canceled, confirmed) and made_by
(online, in_person, phone, mail), while the attributes of the table guest
are: first_name
, last_name
and member_since
. Perhaps you feel like the reservation
table needs more attributes (like room type, number of beds), we will cover this point later, until then, consider our reservation
table incomplete. The following data model created in Vertabelo shows the main tables. Stellar drive defrag v3 0 3.Data Types: What Are the Domains of Allowed Values for a Column?
Note that every column has a data type (varchar, integer, date, boolean) to indicate what kind of values can be assigned to the column. For example, the column
smoke
on table room
is boolean data type, meaning only true or false are the allowed values.Primary Keys: the Social Security Number of Every Record
Every table should have a column (or more than one) acting as an identifier for every record in the table. This column is called the primary key (PK), and best practices on database design suggest that every table must have a PK.
If we take a look on the previous Vertabelo data model, we will see that every table has a column called
id
with a PK indicator on the right. These id columns forms the PK (as a convention we name id
the PK column).An important concept, perhaps obvious for many readers, is that a PK column can’t have duplicated values. In other words, every PK column has a unique constraint, and any attempt to create a new record with a duplicated value will be rejected with an error by the database manager.
Continue Discovering; Find New Database Objects
A reservation is one of the more complex elements to represent in this data model. One reservation can have many rooms associated with it (for example “I wish to make a reservation for one double room and a separate room with 3 beds for my kids”). This business requirement adds 4 things to our model:
A new table: We need to create a new table called
room_reserved
, where we store all rooms belonging to one reservation.Add two references: A reference is a very important element in a data model. A reference describes how one table is related to another table. In our model, every room reserved belongs to one reservation, so we will use a reference to model that fact. This reference is graphically represented as a line connecting both tables.
Moreover, as every reservation belongs to one guest, we need to create a new reference linking the
guest
and the reservation
tables. Move a column: Since we can have several rooms belonging to one reservation, we must allow cancelation per individual room, after that we move the
attribute
status from reservation
to reserved_room
table.The updated data model is showed in the following diagram designed in Vertabelo:
What Happens to the Tables Linked by a Reference?
When we create a reference between two tables, one new column is added to one of the tables. This just added column is called a Foreign Key, and acts as a pointer to the other table allowing connections between tables. For example, take a look to the followings diagrams:
Fig. 1 Tables
reservation
and guest
before and after adding a referenceContinue Discovering; Go for More
![Training Training](https://www.sessionlab.com/wp-content/uploads/Train-the-trainer-Day-3-schedule-Practice-Training-Delivery-1.png)
One point pending to be modeled is the fact that rooms can be in use by some guests for a period of time. To represent this business fact, we added 2 tables:
hosted_at
and occupied_room
.Note that every person who stayed at the hotel will have a record in
hosted_at
. This record will have a reference to the room he/she occupied and to the guest. This is why hosted_at
has a double reference towards guest
and occupied_room
.The table
occupied_room
will have one record per each room being rented, on this record we can find the fields: check_in
and check_out
of type timestamp indicating when the rent begin and finish. A timestamp data type stores a point in time with arbitrary precision. Every occupied_room
record will also have a reference to the room number being rented and indirectly via hosted_at
to the guests who stayed at this room.We also added the table
room_type
to the data model; the idea is to group the rooms by room category or room type. For example “standard one double bed”, “luxury 2 double beds” can be type descriptions. We also have a max_capacity attribute here.Exercises: Database design is an easy to approach discipline, however, it takes time to become a subject matter expert. If you are doing your first steps on database design, please try to complete the current data model to allow:
- If two or more guests are sharing a room, allow different check-in and check-out for each guest.
- In some cases hotels can change the configuration of the rooms (for example from standard one double bed to luxury 2 double beds). Add to the data model the elements to represent those configuration changes, maintaining the history of every room.
Subscribe to our newsletterJoin our weekly newsletter to be
notified about the latest posts.
notified about the latest posts.
You may also like
design_patterns
Logging in With External Services
example_models
How to Design a Database Model for a Movie Theater Reservation System
design_patterns
Email Confirmation and Recovering Passwords
design_patterns
7 Common Database Design Errors
design_fundamentals
Database Design: More Than Just an ERD
Records 1 5 8 – Innovative Personal Database Training Courses
design_patterns
How to Store Authentication Data in a Database. Part 1
others
Basing Database Models in Reality: A Blogger’s Challenge
design_fundamentals
5 Steps for an Effective Database Model
This page contains lessons that may be viewed from this page. Select the title of the lesson. The lesson will open in a new window. If you have a popup blocker, either disable it or permit popups to run from this website.
Instructions on how to download the files.
*Click on the download links to download the material files. Some files are zipped and need to be unzipped. Click the web page file name after unzip to open them. The files with .story file extension need to have Storyline 360 installed to open them.
Level One Lessons are Primarily for Custodians
Code | Title | Description | Source Files (downloadable)
| Date Updated |
---|---|---|---|---|
Terminology | ||||
L1.001 | What is the Records Lifecycle? | Learn about the phases in the records management lifecycle. |
| 8/27/2020 |
L1.002 | What are Temporary Records? | This module defines the concept of temporary records in the world of U.S. Federal records. |
| 8/27/2020 |
L1.003 | What are Permanent Records? | This module defines the concept of permanent records in the world of U.S. Federal records. |
| 8/27/2020 |
Tools | ||||
L1.004 | Records Management Policy | This brief reading provides an overview of records management policies that you should know. |
| 2/5/2020 |
L1.005 | What is a Records Inventory? | This module explains how records inventories are used in records management. |
| 8/28/2020 |
L1.006 | What is a Records Schedule? | Learn about the parts of records schedules and how schedules are used in records management. | 1. LMS/SCORM 2. Storyline Source File 3. Web Package | 8/27/2020 |
L1.007 | What is a File Plan? | This module explains the parts of a file plan. |
| 8/27/2020 |
L1.008 | What's the difference between a records inventory, a records schedule, and a file plan? | This brief reading explains the differences between these three records management tools. |
| 8/27/2020 |
L1.009 | Finding Your Records Control Schedules on NARA's Website | This tutorial shows you how to find your agency's records schedules. |
| 8/27/2020 |
Resources | ||||
L1.010 | The Records Management Team | This brief reading provides an overview of who is involved in helping manage agency records. |
| 7/29/2019 |
L1.011 | Finding Your Agency Records Officer on NARA's Website | This tutorial shows you how to find out who your Agency Records is by using NARA's website. |
| TBD |
L1.012 | Recognizing Records, Non-records, and Personal Files | Learn how to determine if information should be categorized as records, non-records, or personal files. | 1. LMS/SCORM 2. Storyline Source File 3. Web package | 8/31/2020 |
L1.013 | Records, Nonrecords, and Personal Files - Review and Practice | Practice separating records, non-records, and personal papers. |
| 8/31/2020 |
L1.014 | Who has the Records? | Practice identifying records. |
| 8/31/2020 |
Filing, Naming and Metadata | ||||
L1.015 | Separating Files | Learn how to separate information into logical categories for filing. | 9/8/2020 | |
L1.016 | Filing and e-File Naming Conventions | This module explains best practices for naming files. | 9/8/2020 | |
L1.017 | Metadata | This module explains how metadata is used in managing electronic records. | 9/10/2020 | |
L1.020 | Assigning Metadata | Learn about NARA's metadata standards for the transfer of permanent electronic records. | 9/10/2020 | |
Records Inventory | ||||
L1.021 | Level 1 Creation and Receipt - Review | Review the topics covered in the create and receive the section of the first level of the training curriculum. | 9/8/2020 | |
L1.022 | Participate in a Records Inventory | Learn what information needs to be collected during a records inventory. | 9/8/2020 | |
L1.023 | Records Inventory Practice | Practice capturing information about records. | 9/8/2020 | |
Records Clean-up | ||||
L1.024 | Conduct a Records Clean-Up | Learn how to plan and conduct records clean-up in your office. | 9/8/2020 | |
File Plans and Filing | ||||
L1.025 | File Plans | Learn how to update the file plans for your office. | 9/10/2020 | |
L1.026 | File Records | Learn how to accurately file records according to your file plan and records schedules. | 9/10/2020 | |
Electronic Records | ||||
L1.027 | e-File Naming Practice | Practice applying best practices for naming electronic files. | 9/8/2020 | |
L1.028 | Manage Email Records | Learn how to identify and manage records that are in email. | 9/8/2020 | |
Managing Risks to Records | ||||
L1.029 | Identify and Report Risk or Damage to Records | Learn how to identify and mitigate risks to your records. | 9/11/2020 | |
Privacy and FOIA | ||||
L1.030 | Identify Privacy Concerns | This module explains some of the Privacy Act issues related to records management. | 9/10/2020 | |
L1.031 | Manage the Freedom of Information Act (FOIA) Requests | Learn how to records managers provide records for FOIA requests. | 9/10/2020 | |
Disposition | ||||
L1.033 | Calculating Disposition Dates | Learn how to use a records schedule to calculate disposition dates for temporary and permanent records. | 10/26/2020 | |
L1.034 | Store Records On-Site | Learn the best practices for storing records in your office. | 9/10/2020 | |
L1.035 | Transfer Records to Off-Site Storage | Learn the steps you need to take to prepare and transfer records to offsite storage. | 9/10/2020 | |
L1.036 | Create a Box List for Records Transfer | Learn how to create the list of files and records that should be included in each box of records. | 9/14/2020 | |
L1.037 | Manage Records Held Off-Site | Learn how to manage your records that are stored in facilities other than your office. | 9/14/2020 | |
L1.038 | Coordinate Disposition with the RLO | This module explains how to ensure records are destroyed or accessioned once they reach the disposition date. | 9/14/2020 | |
L1.039 | Safeguarding Frozen Records | Learn how to manage records that have the disposition suspended because of legal action or other reasons. | 9/14/2020 | |
L1.040 | Records Lifecycle Review | Review how to manage records throughout the lifecycle. | 9/15/2020 |
Level Two Lessons are Primarily for Liaisons
Records 1 5 8 – Innovative Personal Database Training Programs
Code | Title | Description | Date Updated |
---|---|---|---|
L2.001 | Identify Metadata Needed to Manage Electronic Records | Learn the steps necessary for the identification of keywords and terms that may be used as the basis for assigning metadata to records. | 9/15/2020 |
L2.002 | Establish Naming Conventions for Electronic Records | Learn the best practices for developing and implementing document naming conventions in your office. | 9/15/2020 |
L2.003 | Managing a Shared Drive | Learn how to organize an existing shared drive in a way that allows records liaisons to manage Federal records while complying with key requirements, including proper records disposition. | 9/23/2020 |
L2.004 | Implementing a Local Recordkeeping System | This module explains the steps necessary to transfer existing records into a new record-keeping system so that the files and folders are aligned to your file plan. | 9/23/2020 |
L2.005 | Planning Local Digitization Projects | Learn the steps necessary to plan a scanning project that is cost-effective and efficiently meets all defined organizational and functional requirements for digitization. | 9/23/2020 |
L2.006 | Implementing Updates to Records Schedules | Learn how to recommend changes to your agency records schedule and implement updated schedules. | 9/23/2020 |
L2.007 | Identifying Unscheduled Records | Learn how to identify unscheduled records and systems to ensure the information is retained until the records are scheduled. | 9/23/2020 |
L2.008 | Coordinate Disposition | Learn how to ensure that temporary records are destroyed after the expiration of their assigned records schedule retention period. | 9/23/2020 |
L2.009 | Coordinating the Secure Destruction of Temporary Records and Non-records | This module explains how to coordinate the destruction of temporary paper and electronic records, as well as the destruction of non-records materials. | 9/23/2020 |
L2.010 | Overseeing the Authorized Destruction of Federal Records in Agency Business Units | Learn the best practices for accurately reviewing and approving the disposal records. | 9/23/2020 |
L2.011 | Transfer Permanent Records - Annual Move | This module explains the steps necessary to accession permanent records that are stored in a Federal Records Center. | TBD |
L2.012 | Transfer Permanent Audiovisual Records | This module explains the steps necessary to accession audio-visual records into the National Archives. | 9/23/2020 |
L2.013 | Transfer Permanent Records - Direct Transfer | This module explains the steps necessary to accession records into the National Archives. | 9/24/2020 |
L2.014 | Maintaining an Essential Records Inventory | Learn the steps for updating the essential records list for your office or program. | 9/24/2020 |
L2.015 | Learn how to reduce or eliminate the likelihood of damage to your records. | 9/28/2020 | |
L2.016 | Safeguarding Records in Your Office Part 2 | Learn the steps to take for records are damaged. | 9/28/2020 |
Records 1 5 8 – Innovative Personal Database Training Reliaslearning
Level Three Lessons are Primarily for Agency Records Officers (ARO) | |||
Code | Title | Description | Date Updated |
---|---|---|---|
L3.001 | Drafting a Records Schedule for Submission to NARA | Learn how to write schedule items and the administration information required for proposing a records schedule to NARA. | 9/28/2020 |
L3.002 | Apply the General Records Schedule (GRS) for Agency Records Schedule | Learn how to use the General Records Schedule (GRS) to manage some of your agency records. | 9/29/2020 |
L3.003 | Writing a Big Bucket Schedule | Learn how to write a records schedule that proposes aggregating similar records groups in order to simplify the management of disposition for those records. | 9/29/2020 |
L3.005 | Develop Records Management Strategic Goals and Plan | Learn how to create a strategic plan for your agency records management program. | 9/29/2020 |
L3.006 | Records Management Program Self-Evaluation | Learn how to evaluate your records management program | 9/29/2020 |
L3.007 | Select Sustainable Formats for Long -Term Temporary and Permanent Records | This module explains some of the considerations you need to think about when determining the file formats for your records. | 9/29/2020 |
L3.008 | Establish and Implement a Policy for Managing Records on Shared Drive | Learn how to write a policy to guide how your agency manages records stored on shared drives. | 9/29/2020 |
L3.009 | Email Policy | Learn how to write a policy to guide how your agency manages records stored in email. | 9/29/2020 |
L3.010 | Develop Capstone Approach for the Management of Email | Under development. This module explains some of the considerations you need to think about when implementing the Capstone approach to managing records in email. | 9/29/2020 |
L3.011 | Developing Electronic Records Management Requirements | Learn how to determine what records keeping requirements should be in information systems that are used to manage records. | 9/29/2020 |
L3.012 | Manage a digitization project | Learn how to how to manage digitization projects that are cost-effective and meet agency needs for using or managing the records. | 9/29/2020 |
L3.014 | Developing and documenting Recordkeeping Requirements for the Creation of Agency Records | This module explains some of the considerations you need to think about when determining the agency policies that will determine how records are captured or created. | 9/29/2020 |
L3.015 | This module explains some of the considerations you need to think about when establishing the agency for how records in cloud storage are managed. | 9/29/2020 | |
L3.017 | Establish and implement agency metadata requirements | This module explains some of the considerations you need to think about when establishing the metadata that will be required for agency electronic records. | 9/29/2020 |
L3.018 | Coordinate Records Management Actions with IT | Learn how to work with your IT staff to ensure records management considerations are implemented in agency IT policies and systems. | 9/29/2020 |