8 min read

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:

Designing and Creating Database Tables in Ruby on Rails

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:

Designing and Creating Database Tables in Ruby on Rails
We have completed two out of three steps in designing the E-R model. Next, we have to define how the User entity is related with the Role entity. From the problem statement we can say that a user will definitely have a Role. And the functionality for assigning the Role tells us that a user can have only one Role. So if we combine these two, we can say that ‘A user will have only one Role but different users can have the same Role’. In simple terms, a Role—let us say normal user—can be applied to different users such as John, or Jane. However, the users John or Jane cannot be both normal user as well as administrator. In technical terms, we can say that a Role has a one-to-many relationship with the User entity and a User has a many-to-one relationship with a Role. Diagrammatically, it will be as follows:

Designing and Creating Database Tables in Ruby on Rails

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:

Designing and Creating Database Tables in Ruby on Rails

The final E-R design including all the entities and the attributes will be as follows:

Designing and Creating Database Tables in Ruby on Rails

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

Now, let us visit the Story entity. The attributes of the entity were:

  • 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

The schema has been derived and now we can move to the last part of the database design—creation of the tables.

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here