On a whiteboard (or blank white piece of paper), you should:
Identify each entity (e.g., Song, Game, Person, Customer, Course).
Indicate each entity with a big square box. These boxes will become the tables in your database.
For each entity, list some of the main attributes and their data types (e.g.,
id (INT), name (VARCHAR), date(DATE), amount(FLOAT)). Add a a couple of these
attributes to the box for the entity. The first attribute for each table will
be a "auto-increment integer id".
Draw a line between each entity for which a relationship exists. Indicate
where this is a one-to-many or a many-to-many relationship.
For each many-to-many relationships, create a join table and replace the many-to-many relationship with two one-to-many relationships.
For each one-to-many relationships, add a foreign key. For example, of "TableA" has a foreign key from "TableB", that key should be an integer called "tableBID".
Go back over all of your attributes for all of your tables and add any needed modifiers (e.g., NOT NULL, PRIMARY KEY, UNIQUE, AUTO_INCREMENT, etc.
Finally, transfer your final DB schema to a digital representation using Google Draw, Google Presentation, Powerpoint, Adobe Illustrator, etc.
Table Names should be “Capitalized Camelback”: RecordLabel, Person
All attributes are “lowercase Camelback”: timeOfDay, placeOfBirth
First Attribute is always ID (auto increment integer)
Join Table should be named with a "TO" between entity names: TrackToArtist
Step 2 - Create the DB
As we did in class, create your tables.
Make sure that you also insert some dummy data to make sure that everything works.
This will speed up development later.