Mark’s Site

An ER diagrams (or database diagram) allows you to lay out your entire application’s database schema: the building blocks of your application. And we all know, with poor foundations, buildings crumble. The same is true for applications Put simply, the ER diagram describes the relationships between all the entities in your system.

In later articles, I’ll guide you through my CMSs entities in more detail (yes, the very application that all this is written in!). For now, just know that laying out your database entities in this way is the most important step to your application’s success. You need to have foresight of your application, put yourself in your user’s shoes, and imagine all the ’stuff’ they (and you) will want in terms of functionality.

For example: If your application uses Pages, will you want a page_content variable as well as a page_summary? How long will your database fields be for each table? Will you allow 40 characters or 255 characters for fields like titles and names? Will you want to record the Member who is modifying the Page separately from the Member who created the Page? Will you want to record the Member against the Page at all? What about date stamps for your entities? Do you want to use timestamps or actual datetime information? You will need to consider all of these things when designing your database diagram.

By far, the best tool to create your ER diagram is from MySQL, called MySQL WorkBench (at time of writing, it can be downloaded from here)

Real world example

With the above mentioned software, I created a database diagram for my website, with the relationships between everything, separated into the modules/layers that I wanted (in my case: Security, Members and Pages) I won’t go through it all, you can click on the image and look at all the relationships and fields yourself

Now while that looks pretty compact, that took me the best part of a week at 2-3 hours a night to get it finished. It was a matter of adding an entity, linking it to other tables where necessary, taking a step back and making sure I was doing the right thing in terms of my whole system. You need to be critical of you own application at this early stage. Think ahead by all means, imagine you as a user and as a systems administratory. What fields do all your database entities really need?