Database Design v3 – Quilt

Tracker Database v3 - Quilting
Tracker Database v3 – Quilting

I’m back!  This project has been put on the back burner for a while, but now that it is fall and winter is soon to follow I can’t think of a better time to ramp up development again, especially since a new requirement for my tracker application has arisen!  In chatting with my girlfriend, who is an avid quilter, would like a place to track the quilts she has completed.  Currently this is done by taking a picture of the quilt and storing just the image on her laptop.  She would like to keep track of other information as well, which lead to the addition of a Quilt table (first eight rows) and a Quilt Image Path table (remaining rows) including the following columns:

QuiltIDint(11)Primary Key of Quilt Table, Auto Increment (Also FK of QuiltImagePath table)
UserIDint(11)Foreign Key from User table
Lengthdouble(5,2)Will use inches as unit
Widthdouble(5,2)Will use inches as unit
Patternvarchar(255)The name of the pattern used in creating the quilt
Recipientvarchar(200)Who the quilt was made for
CompletedOndateWhen quilt was finished
Costdecimal(6,2)Total cost of quilt, includes materials, etc.
ImagePathvarchar(255)Path, with file name, to the quilt relative to project
ImagePathIDint(11)Primary Key of Quilt Image Path table, Auto Increment

I thought this would be a nice addition, as it also adds in another technical element I haven’t had to deal with in any of the other items I am tracking.  This is the addition of being able to upload, store, reference, and display an image related to the content.  Also have to implement the functionality to delete a record along with corresponding images.  I believe, without attempting an implementation yet, I am going to accomplish this by:

  • Storing the images in a folder on disk (this folder will be contained within my VS project so I know how to properly reference the folder)
  • Rename the file on upload after record is created to include primary key in file name for if need to cross reference for any reason, also ensures unique names are being stored
  • Storing the relative path (from project perspective) with file name in the database (FilePath column)
  • Implement deleting of a record to also include deleting of the file, on missing files skip and continue with deletion of record

My goal for the winter is to have an initial implementation for all the features I currently have slated for my tracker application.