Blog 10 – DB implementation

Working from the DB design that I have developed I used MySQL Workbench to implement the database. I initial started with the original design and have been updating it along to match changes to the database that I felt were necessary. These changes were highlighted in the previous blog.

To create the database I setup a new schema “prjrfid701” to create the database in. I then used the built in table creator to construct the tables and their relationships. Before the table creator implements the table it shows the SQL that will be used to create it. I copied this down into a sql file so that I could build the database at a later date. I then went to a website called Mockaroo which allows to to create 1000 rows of test data. I used this to build test data in tables I thought would need it, like the person and person_uid tables. The test data will help with creating procedures that will be used to implement the section of the website that I’m going to implement.

12-10-creating-db-1

Creating the tables using the built in table creator. When you have finished it builds the SQL to create the table for you.

12-10-creating-db-2

That the table creator also allows you to add the foreign keys to the attributes. I also set restrictions, like cascade and restrict, on any relationships I felt it was necessary.

12-10-creating-db-3

When the table creator generated the code I copied it down to a sql file so that I could run the file and recreate the tables and relationships when needed.

12-10-creating-db-4

Tables created in the schema

12-10-creating-db-5

I then added in test data to tables that needed it. This helped in creating the procedures.

12-10-creating-db-6

Creating test data for Person

12-10-creating-db-7

Creating the tables using the built in table creator. When you have finished it builds the SQL to create the table for you.

In the next blog I will be creating a series of procedures for a web page proof of concept example.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s