Architect a Content Sharing Application - Part 1 ER (Entity Relationship) Diagrams

Content Sharing Application - Part 1

This is part one of a tutorial for architecting a content sharing application. Part one covers creating an Entity-Relationship (ER) diagram for the system architecture.

Diagram before Coding

I live by this rule! During my professional experience as a systems engineer, the most useful diagrams/documentations I have created are ER diagrams. I can't stress this enough, always create a simple diagram before writing any code. This allows you to carefully consider the functionality and discuss with stakeholders before starting to code. A poor architecture can limit future functionality and make changes difficult or impossible without an entire system overhaul.
An ER diagram identifies your system objects, their attributes, actions taken against these objects, and relationships between the objects. It also paves the way for the code and allows you to quickly convert the diagram into well thought-out and organized code. Another benefit of planning and diagramming before you code is that by outlining the functionality & system constraints with the stakeholders, you have a better chance of preventing scope creep.

ER Diagram for a Simple Content Sharing Application

In this example, we will create a very simple architecture for a content sharing application. This application will have users that can add content and save content to a board. In the next tutorial we will write SQL statements to create this structure in a MySQL Database.

• First, consider the objects in this scenario. We will need: a user, some content, and content boards. On our diagram, we will draw these in rectangular boxes. There are several software tools such as visio that can assist in creating these diagrams. However, I have found for business purposes that Microsoft Powerpoint has the necessary shapes and has a very easy format to style the diagram with shadows and 3D effects. In this example, I will use Powerpoint to create my ER diagram.
  
  

  The Objects

  
  
• Next, consider the actions that can be taken with the objects. A user can create a content board. A user can Submit content. Finally, a user can Save content. On our diagram, we will draw actions with a diamond and connect the objects by these actions.
  
  

  The Actions

  
  
• Next, consider the relationships between the objects for each action. Is it a one-to-one relationship, many-to-one, one-to-many, many-to-many? In the diagram a one is represented with a line perpendicular to the connecting line and a many is represented by two feet. Let's examine each relationship in our example:
  
  1. User Creates Content Board - This is a one-to-many. One user can create many content boards but each content board belongs to only one user.
  2. User Saves Content - This is a many-to-many. A user can save many pieces of content, but this same content can be saved by many users.
  3. User Submits Content - In this example, we will make this a one-to-many relationship. This means that each user can submit multiple pieces of content and each content belongs to one user. Note: In a more complex example, we could have made this a many-to-many. The benefit to that would be that we could tie the same piece of content to many user submissions instead of potentially storing duplicated content under different users.
  
  

  The Relationships

  
  
• Finally, detail each object and determine exactly what attribute you will want to track for each object. Look over each action and determine if you need to store any additional attributes for this action. Connect each attribute to the object or action it applies to. Underline each primary key with a solid line and a foreign key with a dashed line (or write PK/FK). In this step, I try to be as detailed as possible including data types and bounds.
  Primary Keys are used to identify a unique database record. They can't be null and they must be unique for the table. Unlike Oracle where you need to have a Sequence to increment an id, MySQL allows you to add an autoincrement distinction to your primary key. This means that every time a record is created, the primary key field will automatically increment the id to the next numerical value. Autoincrement can only be used with a primary key.
  Foreign Keys are used to identify a reference to a primary key in another table. This is critical because it ensures that data is consistent between tables.
  

  The Finished Diagram

  
  

The completed ER diagram is great to discuss functionality and system constraints/limitations. In the next section of this tutorial, we will convert this diagram into SQL statements to create this structure in a MySQL database.