Data Design Project Overview
Don't be afraid of being wrong.
Phase 1 Assignment
Create a NEW project in GitHub and PhpStorm named data-design. Be sure your deployment is 100% correct and functional.
index.php file for navigation in a new directory called epic in the root of your project. Document your Persona, User Story, Use Case/Interaction Flow, and Conceptual Model in individual files and create links for each file in the /epic/index.php file.
- Define one (1) Persona for your assigned site.
- Define one (1) User Story for your Persona.
- Document one (1) Use Case/Interaction Flow based on your Persona. Be detailed!
- List the specific steps the user must take to arrive at their goal. Include the system responses for each user-initiated action. Use an HTML unordered list.
Phase 2 Assignment
Create an Entity Relationship Diagram and a Conceptual Model based on the Use Case/Interaction Flow you created in Phase 1. Label all strong and weak entities, primary keys, and relations. See examples below. When you're finished, submit the ERD on Slack for instructor approval.
- Break the data presented into entities
- Break the entities into attributes
- Identify all keys: primary keys and foreign keys (AKA relations)
Phase 3 Assignment
Create Data Description Language (DDL) scripts in the form of CREATE TABLE statements. Create a new directory named
/sql. Inside this /sql directory, create a new
.sql file named after your project. Write your DDL scripts there. See example below. Run this code on your personal MySQL database. Once you have it running correctly, submit it for approval on Slack.
Visual Data Design
Example Conceptual Model
Entities & Attributes
- profileId (primary key)
- profileActivationToken (for account verification)
- profileHash (for account password)
- tweetId (primary key)
- tweetProfileId (foreign key)
- likeProfileId (foreign key)
- likeTweetId (foreign key)
- One Profile can write many Tweets - (1 to n)
- Many Tweets can be liked by many Profiles - (m to n)
Example DDL Scripts
-- The statement below sets the collation of the database to utf8 ALTER DATABASE your_database_name_CHANGE_ME CHARACTER SET utf8 COLLATE utf8_unicode_ci; -- this is a comment in SQL (yes, the space is needed!) -- these statements will drop the tables and re-add them -- this is akin to reformatting and reinstalling Windows (OS X never needs a reinstall...) ;) -- never ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever ever -- do this on live data!!!! DROP TABLE IF EXISTS `like`; DROP TABLE IF EXISTS tweet; DROP TABLE IF EXISTS profile; -- the CREATE TABLE function is a function that takes tons of arguments to layout the table's schema CREATE TABLE profile ( -- this creates the attribute for the primary key -- not null means the attribute is required! profileId BINARY(16) NOT NULL, profileActivationToken CHAR(32), profileAtHandle VARCHAR(32) NOT NULL, profileEmail VARCHAR(128) NOT NULL, -- to make something optional, exclude the not null profileHash CHAR(97) NOT NULL, profilePhone VARCHAR(32), -- to make sure duplicate data cannot exist, create a unique index UNIQUE(profileAtHandle), UNIQUE(profileEmail), -- this officiates the primary key for the entity PRIMARY KEY(profileId) ); -- create the tweet entity CREATE TABLE tweet ( -- this is for yet another primary key... tweetId BINARY(16) NOT NULL, -- this is for a foreign key tweetProfileId BINARY(16) NOT NULL, tweetContent VARCHAR(140) NOT NULL, tweetDate DATETIME(6) NOT NULL, -- this creates an index before making a foreign key INDEX(tweetProfileId), -- this creates the actual foreign key relation FOREIGN KEY(tweetProfileId) REFERENCES profile(profileId), -- and finally create the primary key PRIMARY KEY(tweetId) ); -- create the like entity (a weak entity from an m-to-n for profile --> tweet) CREATE TABLE `like` ( -- these are still foreign keys likeProfileId BINARY(16) NOT NULL, likeTweetId BINARY(16) NOT NULL, likeDate DATETIME(6) NOT NULL, -- index the foreign keys INDEX(likeProfileId), INDEX(likeTweetId), -- create the foreign key relations FOREIGN KEY(likeProfileId) REFERENCES profile(profileId), FOREIGN KEY(likeTweetId) REFERENCES tweet(tweetId), -- finally, create a composite foreign key with the two foreign keys PRIMARY KEY(likeProfileId, likeTweetId) );