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.
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:
So, let us follow the steps.
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:
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:
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:
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.
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 |
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 |
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.
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…