Tag Archives: database model

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!

Separation of Concerns: View Model & Database Model

I noticed how when I used scaffolding to generate my MVC5 controller with views using Entity Framework this would only allow me to specify a model representing a table in my database, which is not what I want.  In order to have true separation of concerns I wanted to:

  • Use view models for my views that get validated prior to any database updates
  • Use database models, auto generated, to represent my database and contain the results of queries

I accomplished this by implementing various services which handle the communication with the database.  The controller contains an instance of the service it requires to communicate with the database.  The service queries the database and returns the results to the controller, which then sends the information to the view utilizing the appropriate format.

When querying (performing  select) the database:

  • Resulting database model(s) are converted to the corresponding view model(s), and
  • Returned to the controller, where
  • They are passed through to the view for display

When updating an entry:

  • View Model is passed from the view to the controller
  • Validated using an appropriate validator, I built with Fluent Validation
  • If invalid, error message is returned to the view and displayed
  • If valid, view model is passed to the service to update the corresponding tables in the database
  • In either case if errors occur then the model state is updated and the default view for the action is returned

An example of this is how I manage reading lists.  I have my reading list controller (ReadingListController class) which handles user interactions and manipulates the model.  You can see below that the controller class contains minimal logic and utilizes helper classes to perform the logic. A validator class is used for validating data being passed from the view, when creating/editing a reading list, using Fluent Validation.  This ensures valid data is being entered into the database rather than relying on exceptions being returned on insert/updating of the database due to ill formatted values.

To provide separation of concerns services are used to interact with the database model.  Utilizing a service also means that the controller does not need to know the implementation of the service and how to interact with the database so by extending the particular service interface you can change the implementation, i.e. if I decided to not utilize the Entity Framework anymore and instead use NHibernate.

In the case of reading lists two services are used, the reading list service and the author service. The author service is utilized here so that we do not duplicate code for retrieving authors who have books and specific books for that author based on selection.  I am still working on the view implementation of this.  The reading list service contains the implementation for retrieving:

  • List of reading lists for a particular user (List title, number of novels in the list)
  • A specific reading list (for editing/deleting)
  • Book information for a book in a particular reading list
  • Details for a reading list (Book titles, year, whether they are read, and date finished)

Also an implementation for editing, creating, and deleting a reading list.

[Authorize]
public class ReadingListController : Controller
{
	private readonly IReadingListService _service;
	private readonly IAuthorService _aService;
 
	public ReadingListController() : this(new ReadingListService()) { }
 
	public ReadingListController(IReadingListService service)
	{
		_service = service;
	}
 
	// GET: ReadingList
	public ActionResult Index()
	{
		int userId = GetUserId();
 
		var readingLists = _service.GetReadingListsForUser(userId);
 
		if(readingLists == null)
		{
			ModelState.AddModelError("", "An error occurred in retrieving your reading lists.");
			return View();
		}
 
		return View(readingLists);
	}
 
	// GET: ReadingList/Details/5
	public ActionResult Details(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var readingListDetails = _service.GetReadingListDetails(id ?? 0);
 
		if (readingListDetails == null)
		{
			return HttpNotFound();
		}
 
		return View(readingListDetails);
	}
 
	// GET: ReadingList/Create
	public ActionResult Create()
	{
		return View();
	}
 
	// POST: ReadingList/Create
	// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
	// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
	[HttpPost]
	[ValidateAntiForgeryToken]
	public ActionResult Create([Bind(Include = "ListTitle")] ReadingListModel readingListModel)
	{
		readingListModel.UserId = GetUserId();
		var rlValidator = new ReadingListValidator();
		var results = rlValidator.Validate(readingListModel);
 
		if (results.IsValid)
		{
			var success = _service.CreateReadingList(readingListModel);
 
			if (!success)
				return RedirectToAction("Failed", new FailedModel { Message = "Unable to create reading list, please try again.", Action = "Index" });
 
			return RedirectToAction("Index");
		}
 
		return View(readingListModel);
	}
 
	public ActionResult Failed(FailedModel fm)
	{
		return View(fm);
	}
 
	// GET: ReadingList/Edit/5
	public ActionResult Edit(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var readingList = _service.GetReadingList((int)id);
 
		if(readingList != null)
			readingList.UserId = GetUserId();
 
		//ViewBag.UserID = new SelectList(db.Users, "UserID", "Username", readingList.UserID);
		return View(readingList);
	}
 
	// POST: ReadingList/Edit/5
	// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
	// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
	[HttpPost]
	[ValidateAntiForgeryToken]
	public ActionResult Edit([Bind(Include = "ReadingListID,ListTitle,UserID")] ReadingListModel readingListModel)
	{
		var rlValidator = new ReadingListValidator();
		var results = rlValidator.Validate(readingListModel);
 
		if (results.IsValid)
		{
			var success = _service.EditReadingList(readingListModel);
 
			if (!success)
				return RedirectToAction("Failed", new FailedModel { Message = "Unable to edit reading list, please try again.", Action = "Index" });
 
			return RedirectToAction("Index");
		}
 
		return View(readingListModel);
	}
 
	// GET: ReadingList/Delete/5
	public ActionResult Delete(int? id)
	{
		if (id == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var rlm = _service.GetReadingList((int)id);
 
		if (rlm == null)
		{
			return HttpNotFound();
		}
 
		return View(rlm);
	}
 
	// POST: ReadingList/Delete/5
	[HttpPost, ActionName("Delete")]
	[ValidateAntiForgeryToken]
	public ActionResult DeleteConfirmed(int id)
	{
		_service.DeleteReadingList(id);
		return RedirectToAction("Index");
	}
 
	//Get: ReadingList/DeleteBook/5/4
	public ActionResult DeleteBook(int? readingListId, int? bookId, string bookTitle)
	{
		if (readingListId == null || bookId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var bm = _service.GetReadingListBookInfo((int)bookId, (int)readingListId);
 
		if (bm == null)
		{
			return HttpNotFound();
		}
 
		return View(bm);
	}
 
	// POST: ReadingList/DeleteBook/5
	[HttpPost, ActionName("DeleteBook")]
	[ValidateAntiForgeryToken]
	public ActionResult DeleteBookConfirmed(int readingListId, int bookId)
	{
		_service.DeleteBookFromList(bookId, readingListId);
		return RedirectToAction("Details", new { id = readingListId });
	}
 
	// Get: ReadingLis/AddBook/5
	public ActionResult AddBook(int? readingListId, string rlTitle)
	{
		if(readingListId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var rlam = new ReadingListAuthorsModel
		{
			Authors = _aService.GetAuthorsWithBooks().ToList(),
			ListTitle = rlTitle,
			ReadingListId = (int)readingListId
		};
 
		return View(rlam);
	}
 
	public ActionResult Books(int authorId)
	{
		var books = _aService.GetBooksForAuthor(authorId);
 
		return Json(books, JsonRequestBehavior.AllowGet);
	}
 
	//Get: ReadingList/MoveBook/5/4
	public ActionResult MoveBook(int? readingListId, int? bookId, string bookTitle)
	{
		if (readingListId == null || bookId == null)
		{
			return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
		}
 
		var bm = _service.GetReadingListBookInfo((int)bookId, (int)readingListId);
		var usersReadingLists = _service.GetReadingListsForUser(GetUserId()).Where(x => x.ReadingListId != readingListId);  //Can't move book to itself
 
		if (bm == null || usersReadingLists == null)
		{
			return HttpNotFound();
		}
 
		bm.ReadingLists.ToList().AddRange(usersReadingLists);
 
		return View(bm);
	}
 
	private int GetUserId()
	{
		int userId;
		userId = int.TryParse(User.Identity.Name.Split(':')[0], out userId) ? userId : 0;
		return userId;
	}
}

Validation for creating a new reading list is shown below.  I chose this example since it is a very simplistic validator, to implement.  The only criteria is that the list title has to be 1-255 characters long and the returned result for the number of novels in a list should be greater than or equal to zero, the highlighted rows.

using FluentValidation;
using tracker.Models.View.ReadingList;
 
namespace tracker.Validators.Novels
{
    public class ReadingListValidator : AbstractValidator<ReadingListModel>
    {
        public ReadingListValidator()
        {
            RuleFor(a => a.ListTitle).Length(1, 255).WithMessage("List name needs to be between 1 and 255 characters.");            RuleFor(a => a.NumberOfNovels).GreaterThanOrEqualTo(0).WithMessage("Number of novels was less than 0, please contact admin.");        }
    }
}

In order to utilize my view model instead of the database model for each view I changed the @model parameter to be my view model instead of the database model.   This ensures that the model is populated within the database session prior to being passed back to the view.  An example is the details view for a reading list:

@model tracker.Models.View.ReadingList.ReadingListDetailsModel