Tag Archives: quilt

Tracker to Hobby Tracker: Resurrecting in 2018!

2017 was a very busy year for me with getting engaged in Gatineau, QC followed by tying the not mid-December.  At work I have been working on a mobile application and want to now do this as a mobile application, platform of choice being iOS/Apple, due to what seems to be a more intuitive development process.  This will also allow me to explore, learn and integrate features to give a better end-user experience within the app.

One features in this vein would be when listing out the quilts they are tracking load the items (mainly the featured image, thinking will be the one labeled ‘top’ or an OTB image for in progress projects) as they come into view and have a local cache to preserve bandwidth, perhaps detecting WiFi vs data.

Below is the current schema I am thinking.  I created the tables in DynamoDB as implementing my app using Amazon Web Services as the back-end.

HobbyTracker NoSQL DB v1.0
HobbyTracker NoSQL DB v1.0

Key aspects of this schema that will hopefully allow me to make this application work are as follows:

  • Based on DynamoDB/NoSQL impelentation UserID and CreatedOn date down to the ms will act as the primary key (Partition and Sort key respectively)
  • When a table needs to have a reference to an item in another table it will store the UserId and CreatedOn for that record in the table in the following format: UserId#CreatedOn
    • This will hopefully allow me to parse and retrieve the item when necessary from the other table
  • Tables marked as Public anyone can add items to as we do not need to duplicate authors, etc. and the app will only allow editing of certain properties (i.e. in Author would only be able to edit DOD)
  • Tables marked Protected are for allowing other users to view the entries and comment (i.e. on quilts project or specific images or patterns)
  • DateFinished for a book will allow options like: In Progress, Unknown, or an actual date formatted MM/DD/YYYY
    • May follow similar format for CompletedOn with a Quilt
    • Default to ‘In Progress’

Update January 1, 2018:

Well, I’ve discovered one thing. This implementation that somewhat parallels a relational database to reference related items in another table takes a lot of read/write capacity units (a measure used by AWS to calculate costs) and the free tier only provides 25 which according to them is more than enough for most applications.  Check out the FAQ.

This leads me to believe I need to rewrite the storage structure to store the bulk of the information in a JSON object in a column of type Map.

Thus, based on this from the AWS documentation above I need to figure out how much storage each item would be as a JSON object:

A unit of Write Capacity enables you to perform one write per second for items of up to 1KB in size. Similarly, a unit of Read Capacity enables you to perform one strongly consistent read per second (or two eventually consistent reads per second) of items of up to 4KB in size. Larger items will require more capacity. You can calculate the number of units of read and write capacity you need by estimating the number of reads or writes you need to do per second and multiplying by the size of your items (rounded up to the nearest KB).

Now, I may just be over complicating things but this will hopefully eliminate the need for so many tables, perhaps a column to indicate object type stored as JSON which will be indexed (increases capacity units per table by the way).

To be investigated further at a later date!

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.