VOIP Project: Database modeling - Part 2
As mentioned in the part 1 of this project, the system which is created during this project contains 3 main components and one of them is the database. In this part we will cover the design of the database.
For creation of Entity-Relationship Diagram(ERD) of the database of the system is used the community edition of visual paradigm application.
Below, in the figure 1, is show the diagram of the database used by the system. As shown in the picture in consists in 6 tables: cit_classroom, cit_subject, cit_scheudle, cit_sip_account, cit_consultation, cit_user.
The table cit_classroom as described by its name, contains information about the classroom, labs and lesson classrooms. It has 2 columns, Id, which is of type varchar(10) and contains the name of the classroom, for example: lab2 or D3, which is unique and is this case is used as primary key. The next column is the Capacity, of type int(11), in which is stored information of the number of the students that the classroom can host.
The other table cit_subject, as the name suggests, contains information about the subjects which are taught in the university. This table has three columns, Id, Name and Credits. The column Id is the primary key of this table, it is of type varchar(20), it may have the format Java1. The other column Name is of type varchar(50) and contains the name of the subject, for example: Java Programming or Advanced Java Programming. Credits, of type varchar(50), is the column which contains information for the credits that a subject is worth for. This column in the existing stage of the project has only an informative role which may be expanded in future evolution of the project.
A very important table of this database for the system is cit_schedule which contains 12 columns. This table has a composite primary key, composed by UserName and Subject_Id columns. This two columns are foreign keys of cit_user and cit_subject tables respectively. This table contains another foreign key, Classroom_Id, which makes possible the relation of this cit_schedule table with cit_classroom table.
The column UserName, of type varchar(50), is the unique identifier of the user of the system. It has the same information as the PBX server, where it is used to uniquely identify the user of VOIP service as well. This information is generated automatically by the bitrix24 which hosts the PBX server of the created system, so it is defined by the server and has the format phone1, phone10 etc. User id in combination with the id of the subject uniquely identify a record in this table.
The id of the subject is stored in the column Subject_Id of this table. It is of type varchar(20) as it is in the table cit_subject. As mentioned above this column is part of the primary key and is a foreign key as well.
The other foreign key of this table, Classroom_Id, is used to create the relation between this table and the cit_classroom table. This column as the respective column in the cit_classroom table is of type varchar(10).
The other column, Title, is of type varchar(50) and may contain the name of a project, or the name of a subject depending on the type of the schedule. It may have the format: Java lesson, or Java project.
The next column, Description, contains the description of the schedule. This field is shown as varchar(5000) in the ERD diagram because of a limitation of the modeling tool, but in the MySQL database this field is defined as text. This field is not mandatory so it can remain null as well.
Periodicity of the repetition is defined in the field Periodicity of the table. It is an integer field, int(11), which shows the number of repetition of this activity per week. This field may be null, in case this field is left null or has the value 0, the specific activity does not have any repetition periodicity.
If the periodicity is different from null or 0 in the other column, Day, is/are stored the day(s) in which the activity is repeated during the week. The column Day is of type varchar(100) and it may contain one up to seven days of the week, if the value in the Periodicity column is different from 0 or null, or it may be null if the Periodicity column is 0 or null.
Date is a column of type date, which contains value only if the periodicity is 0 or null, otherwise this columns remains null.
Column StartTime of type time(7) is a mandatory field which can’t be null when an event is defined. It contains the starting time of an event.
The other column of type time(7), EndTime, differently from StartTime column is not mandatory because some events may have an unknown ending time, so this data is not mandatory to be provided when and event is created.
The last column of this table is the column Type, which is of type varchar(10). This column may take two values: Lesson or Project.
The table cit_sip_account contains information closely related to the data that are used in the PBX server. All the information of this table is needed to create the connection between the application and the PBX server. The information in this table may be part of the cit_user table, because between this two tables is an one to one relationship, but because of the logical division that the information have, this information is split in two different tables.
Primary key of cit_sip_account table is UserName column, which in the same time is the foreign key for the cit_user table. This field is of type varchar(50) and as mentioned previously, identifies uniquely the user in the application and the PBX server and is generated by the PBX server.
The DisplayName column is of type varchar(50) and contains the alias of the sip user in the PBX server.
Password is the column which contains the password which makes possible the authentication of the user in the PBX server, this data is defined in the PBX server as well.
Number is another column which stores the information defined in the PBX server. This column of type varchar(20) contains the extension defined in the server. In future evolution this column may contain other numbers outside the intranet, cell phone numbers or land line phone numbers, but in this phase of the project this feature is not implemented yet.
The last column of this table is Domain. This column of type varchar(70) contains the domain in which is hosted the PBX server. The domain in which is hosted the PBX server during the development of this thesis is ip.b24-6738-1457168933.bitrixphone.com, so this information could have been static in this case, but this column gives more flexibility to the system to change the PBX server or add other PBX servers to the system.
In the table cit_consultation is stored the information of consultation that can be held between students and professors.
This table has a composite primary key, composed by Subject_Id and UserName. This two columns are foreign keys of cit_subject and cit_user tables respectively.
The column Subject_Id is of type varchar(20) same as the column Id in the table cit_subject and it makes possible to be created the relation between cit_consultation table and cit_subject table.
UserName column of type varchar(50) enables the creation of relationship between cit_consultation table and cit_user table.
The other column Periodicity of type varchar(100), stores the information of the repetition in format: Once in a week.
Days of the week in which the consultation are held are defined in the Day column of type varchar(100). The format in this table may be like: Monday, Saturday or Friday.
cit_user stores information about the users of the system, information which is used in the application for the identification of the user, differently from the information which is stored in the cit_sip_account which is used for the connection with the PBX server.
The primary key of this table is UserName, of type varchar(50). As mentioned in the previous sessions this information is generated by PBX server.
The other columns, FirstName and LastName of type varchar(50) contain the name and surname of the user.
The last column of this table, Position of type varchar(50), contains the position of the user. This column may take three values: Student, Professor or Administration. This information is used in the application in order to show the user different views in the application, according their position.
Figure 1: Database ERD
The relations between tables, as shown in the figure 3 above, are of type one to many, except the relation between cit_user and cit_sip_account tables, which have an one to one relation between them because one user may have only one account in the system, and every user must have a sip account in order to be able to use the system.
The users of the system may participate in a consultation, but this is not a mandatory fact, because some user may have no consultation, for example, an administrative user may happen that does not need to be part of consultations, or a professor may not have it mandatory to be part of consultation process with students, depending on the contract that they have with the institution, but a student may be part of consultation in different subjects. Because of the arguments given above the relation between cit_user and cit_consultation tables is defined as a one to many relationship, where may participate in zero or n consultations, where n is a number greater than 0.
The relation between cit_user and cit_schedule tables is defined as a one to many relationship where one user may have zero or n activities, because in a given period during the existence of the system, a registered user may have no activities registered in the cit_schedule table, or in another moment the user may be engaged in several activities.
The relation between cit_subject and cit_consultation tables is defined as a one to many relationship where one subject may be part of zero or n consultation, because in one subject may not be consultation in a given period of time, or in another period of time many students may be part of consultations in the same subject.
One subject may be part of many schedules as well, but there might be no activities at all in certain subjects that is why the relation between cit_subject and cit_schedule tables is defined as a one to many relationship where one subject may be part of zero or n schedules.
The relation between cit_classroom and cit_schedule tables is defined as a one to many relationship as well, where in one classroom may take place zero to many activities of the schedule in a given period of time.
In the next part will be shown the implementation of this design.
< Part 1: Introduction Part 3: Database implementation >
(please report broken link in the comment)
For creation of Entity-Relationship Diagram(ERD) of the database of the system is used the community edition of visual paradigm application.
Below, in the figure 1, is show the diagram of the database used by the system. As shown in the picture in consists in 6 tables: cit_classroom, cit_subject, cit_scheudle, cit_sip_account, cit_consultation, cit_user.
The table cit_classroom as described by its name, contains information about the classroom, labs and lesson classrooms. It has 2 columns, Id, which is of type varchar(10) and contains the name of the classroom, for example: lab2 or D3, which is unique and is this case is used as primary key. The next column is the Capacity, of type int(11), in which is stored information of the number of the students that the classroom can host.
The other table cit_subject, as the name suggests, contains information about the subjects which are taught in the university. This table has three columns, Id, Name and Credits. The column Id is the primary key of this table, it is of type varchar(20), it may have the format Java1. The other column Name is of type varchar(50) and contains the name of the subject, for example: Java Programming or Advanced Java Programming. Credits, of type varchar(50), is the column which contains information for the credits that a subject is worth for. This column in the existing stage of the project has only an informative role which may be expanded in future evolution of the project.
A very important table of this database for the system is cit_schedule which contains 12 columns. This table has a composite primary key, composed by UserName and Subject_Id columns. This two columns are foreign keys of cit_user and cit_subject tables respectively. This table contains another foreign key, Classroom_Id, which makes possible the relation of this cit_schedule table with cit_classroom table.
The column UserName, of type varchar(50), is the unique identifier of the user of the system. It has the same information as the PBX server, where it is used to uniquely identify the user of VOIP service as well. This information is generated automatically by the bitrix24 which hosts the PBX server of the created system, so it is defined by the server and has the format phone1, phone10 etc. User id in combination with the id of the subject uniquely identify a record in this table.
The id of the subject is stored in the column Subject_Id of this table. It is of type varchar(20) as it is in the table cit_subject. As mentioned above this column is part of the primary key and is a foreign key as well.
The other foreign key of this table, Classroom_Id, is used to create the relation between this table and the cit_classroom table. This column as the respective column in the cit_classroom table is of type varchar(10).
The other column, Title, is of type varchar(50) and may contain the name of a project, or the name of a subject depending on the type of the schedule. It may have the format: Java lesson, or Java project.
The next column, Description, contains the description of the schedule. This field is shown as varchar(5000) in the ERD diagram because of a limitation of the modeling tool, but in the MySQL database this field is defined as text. This field is not mandatory so it can remain null as well.
Periodicity of the repetition is defined in the field Periodicity of the table. It is an integer field, int(11), which shows the number of repetition of this activity per week. This field may be null, in case this field is left null or has the value 0, the specific activity does not have any repetition periodicity.
If the periodicity is different from null or 0 in the other column, Day, is/are stored the day(s) in which the activity is repeated during the week. The column Day is of type varchar(100) and it may contain one up to seven days of the week, if the value in the Periodicity column is different from 0 or null, or it may be null if the Periodicity column is 0 or null.
Date is a column of type date, which contains value only if the periodicity is 0 or null, otherwise this columns remains null.
Column StartTime of type time(7) is a mandatory field which can’t be null when an event is defined. It contains the starting time of an event.
The other column of type time(7), EndTime, differently from StartTime column is not mandatory because some events may have an unknown ending time, so this data is not mandatory to be provided when and event is created.
The last column of this table is the column Type, which is of type varchar(10). This column may take two values: Lesson or Project.
The table cit_sip_account contains information closely related to the data that are used in the PBX server. All the information of this table is needed to create the connection between the application and the PBX server. The information in this table may be part of the cit_user table, because between this two tables is an one to one relationship, but because of the logical division that the information have, this information is split in two different tables.
Primary key of cit_sip_account table is UserName column, which in the same time is the foreign key for the cit_user table. This field is of type varchar(50) and as mentioned previously, identifies uniquely the user in the application and the PBX server and is generated by the PBX server.
The DisplayName column is of type varchar(50) and contains the alias of the sip user in the PBX server.
Password is the column which contains the password which makes possible the authentication of the user in the PBX server, this data is defined in the PBX server as well.
Number is another column which stores the information defined in the PBX server. This column of type varchar(20) contains the extension defined in the server. In future evolution this column may contain other numbers outside the intranet, cell phone numbers or land line phone numbers, but in this phase of the project this feature is not implemented yet.
The last column of this table is Domain. This column of type varchar(70) contains the domain in which is hosted the PBX server. The domain in which is hosted the PBX server during the development of this thesis is ip.b24-6738-1457168933.bitrixphone.com, so this information could have been static in this case, but this column gives more flexibility to the system to change the PBX server or add other PBX servers to the system.
In the table cit_consultation is stored the information of consultation that can be held between students and professors.
This table has a composite primary key, composed by Subject_Id and UserName. This two columns are foreign keys of cit_subject and cit_user tables respectively.
The column Subject_Id is of type varchar(20) same as the column Id in the table cit_subject and it makes possible to be created the relation between cit_consultation table and cit_subject table.
UserName column of type varchar(50) enables the creation of relationship between cit_consultation table and cit_user table.
The other column Periodicity of type varchar(100), stores the information of the repetition in format: Once in a week.
Days of the week in which the consultation are held are defined in the Day column of type varchar(100). The format in this table may be like: Monday, Saturday or Friday.
cit_user stores information about the users of the system, information which is used in the application for the identification of the user, differently from the information which is stored in the cit_sip_account which is used for the connection with the PBX server.
The primary key of this table is UserName, of type varchar(50). As mentioned in the previous sessions this information is generated by PBX server.
The other columns, FirstName and LastName of type varchar(50) contain the name and surname of the user.
The last column of this table, Position of type varchar(50), contains the position of the user. This column may take three values: Student, Professor or Administration. This information is used in the application in order to show the user different views in the application, according their position.
Figure 1: Database ERD
The relations between tables, as shown in the figure 3 above, are of type one to many, except the relation between cit_user and cit_sip_account tables, which have an one to one relation between them because one user may have only one account in the system, and every user must have a sip account in order to be able to use the system.
The users of the system may participate in a consultation, but this is not a mandatory fact, because some user may have no consultation, for example, an administrative user may happen that does not need to be part of consultations, or a professor may not have it mandatory to be part of consultation process with students, depending on the contract that they have with the institution, but a student may be part of consultation in different subjects. Because of the arguments given above the relation between cit_user and cit_consultation tables is defined as a one to many relationship, where may participate in zero or n consultations, where n is a number greater than 0.
The relation between cit_user and cit_schedule tables is defined as a one to many relationship where one user may have zero or n activities, because in a given period during the existence of the system, a registered user may have no activities registered in the cit_schedule table, or in another moment the user may be engaged in several activities.
The relation between cit_subject and cit_consultation tables is defined as a one to many relationship where one subject may be part of zero or n consultation, because in one subject may not be consultation in a given period of time, or in another period of time many students may be part of consultations in the same subject.
One subject may be part of many schedules as well, but there might be no activities at all in certain subjects that is why the relation between cit_subject and cit_schedule tables is defined as a one to many relationship where one subject may be part of zero or n schedules.
The relation between cit_classroom and cit_schedule tables is defined as a one to many relationship as well, where in one classroom may take place zero to many activities of the schedule in a given period of time.
In the next part will be shown the implementation of this design.
< Part 1: Introduction Part 3: Database implementation >
(please report broken link in the comment)
Comments
Post a Comment