Building First Database Design from the Bottom-up

As part of course requirement for NJIT’s graduate program, Data Management System Design; we were required to design and build a database that answered the needs of a factitious organization called the Newark Medical Associates.

Working in concert with my project partner, Yassine Chafi Berrehoumawe conducted an analysis to determine what the needs and requirements were.  Those requirements were outlined in the following:


Once we understood the requirements, we began working through our conceptual schema design.  We looked at the various database structures, semantics, interrelationships, and constraints.  We used a top-down approach, starting with abstraction and refining as we progressed.

MySql Workbench played a key role in developing our ER model from which we would eventually build our physical database.

ER Diagram

Clinic DB report

With these two diagrams completed and with an understanding of the database design requirements, we proceeded to build our database using PHP script.  Our initial efforts were to create our databases with sql statements embedded in the php scripts.  However, as the complexity of our design evolved, we found it more efficient to use MAMP and phpMyAdmin to directly build the databases.

We initially used localhost as our test server prior to uploading live on a website that we bought.

We began with 12 tables and ended with 28 as we continually refined our database and sought greater levels of normalization.

In addition to the database, we also needed to build a web application to access the database.  The requirements were as follows:


In building our web application, we decided to build our site from scratch.  This meant buying a website and writing the necessary scripts to make it run.  Although templates, apps and programs exist that would have made this an “easier” process; we felt it would be a greater learning experience to go from the bottom up.

And it was.  Our website is fairly basic, yet we were able to build a good deal of functionality to it.  We learned the process of using foreign keys to populate multiple tables simultaneously.  Entering data into one page, such as “schedule surgery” would call most current information on surgeons, nurses, etc. to populate that table.

The complete code is located here:

Database Design

All in all, working on this database design project was a tremendous, learning experience.  As my skill and knowledge levels increase, I’ve no doubt that I will building better and more comprehensive programs.  That said, I think this was a good start.  Take a look, let me know what you think.


Next up…

Challenges in writing the php scripts…


unsplash-logoShendy Subroto