Background Information
The User Management Module is created for a website called ‘TaleWiki’. TaleWiki is a website about user submitted tales and stories, which can be added, modified, deleted, and published by the user, depending on the Role or Privileges the user has. Taking into consideration this small piece of information, we will design and create tables that will become the back-end for the User Management functionality.
Designing the Tables
To Design and to create tables, we need to understand the entities and their relationship, the schema corresponding to the entities, and then the table creation queries. If we go step-by-step, we can say that following are the steps in designing the tables for the User Management module:
- Designing the E-R model
- Deriving the Schema from the E-R model
- Creating the Tables from the Schema
So, let us follow the steps.
Designing the E-R Model
To design the E-R model, let us first look at what we have understood about the data required by the functionalities, which we just discussed. It tells us that ‘only the Users with a particular Role can access TaleWiki’. Now we can consider this as our ‘problem statement’ for our E-R model design. If you observe closely, the statement is vague. It doesn’t tell about the particular Roles. However, for the E-R design, this will suffice as it clearly mentions the two main entities, if we use the E-R terminology. They are:
- User
- Role
Let us look at the User entity. Now this entity represents a real-world user. It is not difficult to describe its attributes. Keeping a real-world user in mind and the functionalities discussed for managing a user, we can say that the User entity should have the following attributes:
- Id: It will identify the different users, and it will be unique.
- User name: The name which will be displayed with the submitted story.
- Password: The pass key with which the user will be authenticated.
- First name: The first name of the user.
- Last name: The last name of the user. The combination of the first and last name will be the real name of the user.
- Age: The age of the user. This will help in deciding whether or not the user is of required age which is 15.
- E-mail id: The email id of the user in which he/she would like to get emails from the administrator regarding the submissions.
- Country: To keep track of the ‘geographic distribution’ of users.
- Role: To know what privileges are granted for the user. The Role is required because the problem statement mentions “User with a particular Role”.
The entity diagram will be as follows:
Next, let us look at the Role entity. Role, as already discussed, will represent the privileges a user can have. And as these privileges are static, the Role entity won’t need to have the attribute to store the privileges. The important point about the static privileges that you have to keep in mind is that they will have to be programmatically checked against a user. In other words, the privileges are not present in the database and there can only be a small number of Roles with predefined privileges. Keeping this in mind, we can say that the Role entity will have the following attributes:
- Id: The unique identification number for the Role.
- Name: The name with which the id will be known and that will be displayed along with the user name.
The entity diagram for Role entity will be as follows:
One piece of the puzzle is still left. If you remember there is one more entity called Story. We had found that each story had a submitter. The submitter is a user. So that means there is a relationship between the User and the Story entity. Now, a user, let us say, John or Jane, can submit many stories. However, the same story cannot be submitted by more than one user. On the basis of this we can say that a User has a many-to-one relationship with a Story and a Story has a many-to-one relationship with a User. According to the E-R diagram it will be as follows:
The final E-R design including all the entities and the attributes will be as follows:
That completes our E-R design step. Next, we will derive the schema from the
E-R model.
Deriving the Schema
We have all we need to derive the schema for our purpose. While deriving a schema from an E-R model, it is always a good choice to start with the entities at the ‘one’ end of a ‘one-to-many’ relationship. In our case, it is the Role entity. As we did in the previous chapter, let us start by providing the details for each attribute of the Role entity. The following is the schema for the Role entity:
Attribute |
Data type of the attribute |
Length of the acceptable value |
Id |
Integer |
10 |
Name |
Varchar |
25 |
>Next, let us look at the schema of the User entity. As it is at the ‘many’ end of the ‘one-to-many’ relationship, the Role attribute will be replaced by the Id of Role. The schema will be as follows:
Attribute |
Data type of the attribute |
Length of the acceptable value |
Id |
Integer |
10 |
User name |
Varchar |
50 |
First name |
Varchar |
50 |
Last name |
Varchar |
50 |
Password |
Varchar |
15 |
Age |
Integer |
3 |
e-mail id |
Varchar |
25 |
Country |
Varchar |
20 |
Id of the Role |
Integer |
10 |
- Id: This is the Primary key attribute as it can uniquely identify a story.
- Heading: The title of the story.
- Body text: The body of the story.
- Date of Submission: The day the user submitted the story.
- Source: The source from where the story was found. If it is written by the user himself/herself, the source will be the user’s id.
- Genre: The category of the story.
- User: The user who submitted the story.
Name of the attribute |
Data type of the attribute |
Length of the acceptable value |
Id |
Integer |
10 |
Title |
Varchar |
100 |
Body Text |
Varchar |
1000 |
Date of Submission |
Date |
|
Source |
Varchar |
50 |
Status |
Varchar |
15 |
Id of Genre |
Integer |
10 |
Id of the User |
Integer |
10 |
Creating the Tables
Looking at the schema required for tables in Ruby on Rails, here is the table creation statement for the Role schema:
CREATE TABLE `roles` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 25 ) NOT NULL ,
`description` VARCHAR( 100 ) NOT NULL
) ENGINE = innodb;
Next comes the table creation statement for the User schema. Note that here also we are following the one-to-many path, that is, the table at the ‘one’ end is created first. Whenever there is a one-to-many relationship between entities, you will have to create the table for the entity at the ‘one’ end. Otherwise you will not be able to create a foreign key reference in the table for the entity at the ‘many’ end, and if you try to create one, you will get an error (obviously). So here is the create table statement for the User schema:
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`user_name` VARCHAR( 50 ) NOT NULL ,
`password` VARCHAR( 15 ) NOT NULL ,
`first_name` VARCHAR( 50 ) NOT NULL ,
`last_name` VARCHAR( 50 ) NOT NULL ,
`age` INT( 3 ) NOT NULL ,
`email` VARCHAR( 25 ) NOT NULL ,
`country` VARCHAR( 20 ) NOT NULL ,
`role_id` INT NOT NULL,
CONSTRAINT `fk_users_roles` FOREIGN KEY (`role_id`) REFERENCES `role`( `id`) ON DELETE CASCADE
) ENGINE = innodb;
Next, let us create the table for Story, we will call it the ‘tales’ table, we will also add a foreign key reference to the users table in it. Here is the query for creating the table
CREATE TABLE `tales` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR( 100 ) NOT NULL,
`body_text` TEXT NOT NULL,
`submission_date` DATE NOT NULL,
`source` VARCHAR( 50 ) NOT NULL,
`status` VARCHAR( 15 ) NOT NULL,
`genre_id` INT NOT NULL,
`user_id` INT NOT NULL,
CONSTRAINT `fk_tales_genres` FOREIGN KEY (`genre_id`) REFERENCES genres( `id`)
) ENGINE = innodb;
Next, we will make a reference to the users table after executing the above query, with the following query:
ALTER TABLE `tales` ADD FOREIGN KEY ( `user_id` ) REFERENCES `users` (`id`) ON DELETE CASCADE ;
That completes our task of creating the required tables and making necessary changes to the tales table. The effect of this change will be visible to you when we implement session management in the next chapter. And incidentally, it completes the ‘designing the tables’ section. Let us move onto the development of the user management functionality.
Summary
In this article, we learned how to design and create tables for a User Management Module in Ruby on Rails. We looked at designing the E-R model, deriving the schema from the E-R model and creating the tables from the schema.