Database Creation Queries

Having already designed the database and built a schema for reference, it's time to really build the database in MySQL Workbench. I'll be hosting the database local to my computer for the time being, but would love to host in the cloud eventually.

We have many pieces of the pie to create, namely:

• Tables with fields, keys, constraints, and relationships
• Views to consolidate data in various tables into a single, manageable query
• Field constraints to ensure the integrity and consistency of the database
• Functions to grab and return record keys
• Procedures to make adding records easier

I run into various issues creating these pieces and ensuring they work properly. I break down the issues and share the solution I came up with.

To view the entire script of querries, select the "View Project Resources" at the top of the post.

Tables

Creating tables was the most basic and straightforward approach. I've written and executed enough to know what to expect. Still, issues arose.

The "wItem" table was a little tricky because there were several things I wanted to happen. I wanted the "active" column to default to 'True', I wanted the "size" to default to 'Null', and the "qtyOwned" to default to 1. That was pretty straight forward using "DEFAULT" after specifying the data type.

There was a single primary key and three foreign keys. I tried to name the keys with a consistent structure: "FK_Item_ItemOwnerId" first tells us it's a foreign key ("FK"), then that the table is "Item", and the field is "itemOwnerID". The different cases help distinguish a constraint name from a field name.

Finally, I wanted the items to start from a specific index and I made that happen on the second to final line: "AUTO_INCREMENT = 101" tells the table to begin from index 101.

Outside of those minor items to work out, creating tables was smooth sailing.

Views

Despite using views frequently throughout my experience with databases, I've never created one.There was zero learning curve, as it turns out. Upon designing the "SELECT" query I wanted, it was a simple matter of nesting the script in the "CREATE VIEW" statement.

For most views, I needed to collapse multiple entries relating to a single item into a single row. To do so, I used a neat little function native to MySQL called "GROUP_CONCAT". See below:

The function allows us to keep every row a unique fit record and put all of the clothing items from the fit into a single column separated by a ",". See an example of the output we can achieve:

With this trick, all of the views were simple to construct.

Field Contraints

I had some critical constraints I wanted implemented. One in particular that proved difficult was for the "wColor" primary key 'hexCode'. As you may be familiar, Hexadecimal Color Codes are six characters long preceded by a '#' sign. I wanted to enforce these conditions.

The major issue lied in that more complex field constraints weren't allowed as inline. Furthermore, even a typical "ALTER TABLE ___ ADD CONSTRAINT" wasn't working. Upon investigation, I found that MySQL doesn't support those constraints.

I was able to skirt the issue using a "TRIGGER", as shown below:

The logic here is fairly straight forward. Before we insert a record, we first check the conditions with an if statement. If the hex code is 7 characters long and begins with a '#', the database will insert the record. If not, it'll throw the customer error message.

There is more advanced validation to implement here. For example, only letters 'A - F' are permitted. Rarely would anyone make up their own hex code off the top of their head, so this validation is just to make sure the user is protected from any typing/copying errors.

Once this approach was working, implementing the other field constraints for '0 - 1' values on materials, colors, and patterns and that their sum never exceeded 1 was straight forward.

Functions

The functions I had in mind to begin with were really designed to make procedures easier. The functions I created all take in a record name (relating to an alternate key of some sort) that is used to return the record id from the table. The procedures use these ids to perform repeatable tasks.

Refer to the "Project Resources" for full details. We'll focus on the procedures.

Procedures

I was taught that procedures are an approach to encapsulating database modification tasks and enforcing standard practice. They're especially useful when databases feed a front-end application, so that programers can use the procedure statements and pass the requisite parameters.

I do envision a front-end at some point, however, procedures will simplify the crude manner in which I'll initially interact with the database.

Here's an example of a procedure to add a brand name to the database:

The procedure takes care of the repetitive query to add a record and allows us to just pass it the necessary parameters.

With the database structure created per the schema, we'll be able to start loading in wardrobe data. We'll need to record clothing items to then facilitate adding fit records.

Ultimately the goal is to turn this data into insights about that I do/don't wear, what my preferences are about patterns, etc. We'll be one step closer to this type of analysis with complete data on the current status of my wardrobe and regular logging of my daily fit choices.