Architect a Content Sharing Application - Part 2 Creating Tables in MySQL

Content Sharing Application - Part 2

This is part two of a tutorial for architecting a content sharing application. Part one covers creating an Entity-Relationship (ER) diagram for the system architecture.
This tutorial covers converting the ER diagrm from part one into SQL statements to create the architecture in a MySQL database.

The Diagram

Create tables for the objects

  • The first thing to keep in mind is that each object in the diagram will need a table structure in the database.
  • Check for foreign key constraints! If a table has a foreign key, that means it is dependent on the referenced primary key from another table. That table needs to be created first otherwise you will receive an error that the referenced field/table does not exist.
Let's consider our example in the following order...
  1. User - We will create the user table first since user_id is a foreign key for both other objects.
  2. Content - Since content needs to exist before it can be placed on a board, we will create the content table next.
  3. Content Board - Finally, we will created a table for the content board object.


Since we are using a MySQL database in our example, we will use data types and syntax applicable to this database. MySQL offers an autoincrement option for the primary key of a table. This automatically increments the value of the primary key every time a record is created. We will use this on our primary keys.
Since our data types and length constraints were already listed on our ER diagram, this step should be very easy. We will use simple CREATE statements to build our tables exactly as we planned them in the diagram.

User


CREATE TABLE USER 
(
   USER_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   USER_FNAME VARCHAR(50), 
   USER_LNAME VARCHAR(50), 
   USER_EMAIL VARCHAR(100), 
   USER_PASSWORD VARCHAR(50)
);

Content


CREATE TABLE CONTENT
(
   CONTENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   USER_ID INT,
   CONTENT_DESCRIPTION TEXT,
   CONTENT_URL TEXT,
   CONTENT_TYPE VARCHAR(1),
   SUBMIT_DATE DATETIME,
   FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID)
);

Content Board


CREATE TABLE CONTENT_BOARD
(
   BOARD_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   USER_ID INT,
   BOARD_TITLE VARCHAR(200),
   IS_PUBLIC VARCHAR(1),
   FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID)
);

Create tables for the relationships

Finally, we will need to create helper tables for any of the many-to-many relationships. As you may have noticed when we created our object tables, the one-to-many relationships link together with the use of a foreign key. When naming these helper tables, I use the name of the convention: first object name_second object name. Additionally, we need to include fields for any information we tied to the action.

USER_SAVES_CONTENT

CREATE TABLE USER_SAVES_CONTENT
(
   USER_ID INT,
   CONTENT_ID INT,
   BOARD_ID INT,
   SAVE_DATE DATETIME,
   FOREIGN KEY (USER_ID) REFERENCES USER(USER_ID),
   FOREIGN KEY (CONTENT_ID) REFERENCES CONTENT(CONTENT_ID),
   FOREIGN KEY (BOARD_ID) REFERENCES MEDIA_BOARD(BOARD_ID)
);

That's it! These are the table structures we will need for the content sharing application. Follow along to create PHP services to insert and retrieve data from our structure for the application.

Comments

Post a Comment