Phone System Database: The Beginnings
Saturday, May 29, 2010 17:05Overview
Starting last summer I have been assisting with the VoIP roll-out. I am now back for another summer and have obtained a project that has the potential to be exciting and a learning experience, which enhances the working experience. Learning does not stop when you leave the classroom, but picks up and becomes more exciting as you embark are interesting (though not always) projects.
My task is to design and implement a database which can be used to store data relevant to our phone system infrastructure of production units and not an inventory system. This will include appropriate server and switch information, phone and fax line information as well as a place to make comments that are dated to keep track of changes or other meaningful information related to a particular phone system.
For this project I am using an Apache webserver, MySQL 5.0 for the database running on Linux (CentOS 5.4) and MySQL Workbench to create a preliminary Enhanced Entity Relationship Diagram (EERD).
It is necessary to start with a logical representation of your database before moving on to the physical representation unless you are 100% confident when to use a natural or surrogate primary key, foreign keys, and composite keys within your tables. I suggest always starting with a logical model because this allows you to normalize and potentially catch any glaring design flaws or oversights.
Phase 1: The Design
Figure 1: Phone System Database EERD
Naming Conventions
You should decide on a naming standard to implement across the database for your tables and there columns. This keeps things consistent and makes it easier for future individuals to follow. Typical naming schemes are all lower case using an underscore to separate words (i.e.: phone_system) or to capitalize the first letter of each word (i.e.: PhoneSystem).
Natural or Surrogate Primary Keys?
When researching this topic there appears to be valid reasoning for using a natural primary key (PK) (a column storing application data that will always be unique per entry) over a surrogate key (not derived from application data but will act as the unique identifier), which typically is an auto-incremented integer.
I went with surrogate keys throughout because of the processing time required for queries to compare strings of varying lengths and greater than five or my table did not include any other unique column. If tables exist where a natural key can be justified then do so as you do not need to use one method exclusively (which is impractical) within a database. As an example for a customer table you could use a customer number or social insurance numbers as the PK, whereas in an address table you would have to use multiple (or all) columns to create a unique primary key making a surrogate key more reasonable.
When using surrogate keys as your primary keys, they should be of type integer and automatically incremented to insure uniqueness by allowing the PKs to be handled by the DBMS.
Identifying or Non-Identifying Relationships?
Non-identifying relationships (represented by dotted lines in the EERD) do not enforce data integrity and uses the primary key of one table as the foreign key in the other. In a case where it is a one-to-many non-identifying relationship the primary key on the one side becomes the foreign key of the table on the many side. I use this type most often as there is no direct dependency on the foreign table.
Identifying relationships (represented by solid lines in the EERD) lead to composite primary keys and can lead to a composite key spanning six or seven columns, which can become very messy for coding so I use them cautiously. You should only use this relationship when your table requires the identifying relationship (hence the name) as part of its identity. That is when the information from the other table is required as part of the entity. In my case, I chose to make the relationships from the manu_model table identifying because every table it relates to requires/has a manufacturer and model.
Database Engine
The database engine (or “storage engine”) is set for each table either manually or by using the default storage engine, typically MyISAM for MySQL. The database engine is the underlying software component that a database management system (DBMS) uses to create, delete, retrieve, and update data from a database. This option affects things such as performance and data consistency.
Briefly, InnoDB is ACID compliant, fast for updating and uses row level locks making it suitable for transactions, whereas MyISAM is fast for reading and inserting using table level locks which make it impractical for transactions. InnoDB allows cascading deletes/updates whereas stored procedures or code you have written is required for MyISAM, making it ideal to use InnoDB for some tables while using MyISAM for others.
You are able to change from one MySQL storage engine to another, which is a nice feature, if it is unavoidable. This can be done using the ALTER TABLE statement via the mysql> directly on the server, through ssh or via phpMyAdmin.
Example 1:
ALTER TABLE myTable ENGINE = MYISAM;
ALTER TABLE myTable TYPE = INNODB;
The statements in Example 1 would change your MySQL database engine from MyISAM to InnoDB. When considering a change you have to ensure it is necessary because converting large tables between engine types takes a lot of computer resources for large tables. This is where you hope good planning from the beginning will pay off and you start with the most appropriate table type.
For other database engine types and further information check out http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html (change versions if necessary).
Views
In figure 1, you can see a bunch of views that I have created (as named yellow ‘ovals’), which allow me to gather data from multiple tables into one place for easy viewing and updating of relevant information based on the criteria I specify in my SQL statements, and give the columns easier to read and understand names when necessary. As an example:
CREATE VIEW `phsysdb`.`tower_info` AS
SELECT srv_name AS 'Server_Name', manufacturer AS 'Manufacturer', model AS 'Model', ram_gb AS 'RAM (GB)', cpu_speed AS 'CPU Speed', num_processors AS '# of Processors', num_power_supplies AS '# of Power Supplies'
FROM phone_system PS, hardware H, manu_model M
WHERE PS.ph_system_id = H.ph_system_id AND M.manu_model_id = H.manu_model_id
ORDER BY srv_name;
Creates the view tower_info which shows relevant columns from the ph_system, manu_model, and hardware tables based on the equality of the primary/foreign key pair to match the data correctly and then ordered by the server name.
Finalizing the Design
Once you have your logical design completed you should perform some tests by inserting some data into the tables. Make sure that each column is storing only one thing and should not be broken into two or more columns and that each table is only representing one entity. Essentially you will want to perform the process of database normalization.
Conclusion
Now that I have completed my logical model (Phase 1) it is time to move on to Phase 2 (Logical to Physical), which is to create the physical model.
Phase 2: Logical to Physical
Now that I have a logical representation you need to make it become a reality. If you are using MySQL Workbench than you can connect to your database server via Database -> Synchronize Model and your schema name should match up with the database you created on the server if you have already.
The way that worked best for me, as it gives you a better idea of errors, and made it easier for me to debug my model is to get the SQL commands to execute yourself on the server. This is done via Database -> Forward Engineer, select the objects you wish to include, and then in the review phase you can save the script to a .sql file. Once you upload the .sql file, execute it on your server, within the directory the file resides, with the command:
mysql --user=username --pass=password database_name < filename.sql
If the design of your database (and SQL statements) are syntactically correct there will be no errors, otherwise, you will have to check into the errors and correct your model accordingly and repeat Phase 2. If you have phpMyAdmin installed it allows you to import your database using a GUI.
If you wish to go into greater depth you can design and write the SQL statements yourself to create your tables.
Conclusion
Now that I have completed Phase 2, and have all our tables stored in the database, I need some way of managing the data (from an end-users perspective). This will be done in Phase 3 by creating my own front-end, or finding an open-source project that I can use and modify to my liking. In my case I want it to be web based.