HEJP's Blog

The first step to building a database is to determine the types of resource or objects it is going to contain and the relationships between the objects. The Scriblet database will contain the following:

• User: user_id, name, password, etc
• Project Book: project_book_id, user_id, title, public, etc
• Project Book Item: project_book_item_id, project_book_id, title, contents, public, etc
• Tag: tag_id, user_id, name, etc
• Tag Map: tag_map_id, tag_id, project_book_item_id, etc
• Follower: tag_map_id, tag_id, project_book_item_id, etc
• Access: tag_map_id, tag_id, project_book_item_id, etc

And the relationships between them are:

• User has many Project Books
• User has many Tags
• Project Book has many Project Book Items
• Project Book Item has many Tag Maps
• User has many Followers

The Tagging System

The tagging system I’ve chosen is the Toxi solution thought up by toxi, here now. The toxi solution separates the tags into a table and in order to tag an item, a project item say, you create a tagmap entry connecting the item and the tag, hopefully Fig. 1 will enlighten things. The are a couple of other solutions described here, but they’re pretty flawed.

Access

I’d like it to be possible for the user to create a project book and specify whom they wish to be able to see or edit it. One way I was thing of was to have a ‘public_to’ field in the PROJECT_BOOK table, which will indicate that the project book is private, viewable by people the user is following or viewable by everyone. But this is rather limited you can’t, for instance, say I wish for Joe Bloggs to see this plus I also wish for the user to determine who can edit it.

To do this I could introduce an extra table ACCESS, which give a particular user access either to edit or view a certain project book. However, I can imagine this will produce anything other than clumsy or even down right ugly SQL statements. For example selecting all project books that can edit would look something like this:

	SELECT * 
	FROM `PROJECT_BOOK` 
	LEFT JOIN `ACCESS` ON `PROJECT_BOOK`.`project_book_id` = `ACCESS`.`project_book_id` 
	WHERE `PROJECT_BOOK`.`user_id` = $other_guys_id
	AND (
		`PROJECT_BOOK`.`public_to` = 2
		OR `PROJECT_BOOK`.`public_to` = 1 AND EXISTS(
			SELECT * 
			FROM `FOLLOWER` 
			WHERE `user_id` = $me AND `follower_id` = $other_guys_id
		)
		OR `ACCESS`.`public_to` = 0 AND `ACCESS`.`user_id` = $me 
	)

Not much of a looker. The query goes as follows is the project book viewable by everybody? (Yay/Damn) Is it viewable by people the user follows and am I one of those? (Yay/Damn) Finally, have I been given special access? (Yay/Damn). The query isn’t completely horrible but I think there are other problems such as should the project book be in a users list of project books as though they are the owner?

The problem with removing the ‘public_to’ field and using of the ACCESS table is that it would become difficult to allow access to all followees. However, the solution as it stands makes it difficult to add a group this isn’t all of the people followees. Hmm this is making me think a group access system would be best, like Google+s circles. A user defines a group and adds access permissions to that group.

For now I shall carry on with the non-follower stuff (the unfriendly bit?), since writing code is good and the follower stuff can be tacked on later. I shall keep this problem rattling round my head for now.

The Database

This entity relationship (ER) diagram rather succinctly sums up the structure of the database. The diagram was produced using the MySQL Workbench, which is a really useful tool for creating databases.

It’s likely some adjustments will be made later.

This series is about the process of building a web app: deciding on which tools, building the front and back end, designing the experience, monetisation, every aspect required.

The Web App: ‘Scriblet’

The web app itself is Scriblet which is a simple project notebook application. The app will be that place where you put all of the images, URLs and thoughts you have while working on a project. As such there will be a number of ways of adding items to a notebook from a web and Android (possibly) interface to a Chrome extension, which I think could be very useful.

Considerations

The usual considerations, such as speed and compatibility with as many browsers as possible, are very important. The plan is to have a layered structure to the front end so, for instance, if IndexedDB is available use it to improve the experience otherwise fallback to using purely AJAX requests. Everybody gets an experience but the people using the modern browsers get the best experience.

A large benefit can be gained from minifying code and combining it into one file. This video, JavaScript Programming in the Large with Closure Tools, has convinced me that Closure Tools is the way to go due the powerful Closure Compiler.

Closure Tools

The Closure Tools is a set of tools developed by Google that ease the process of building fast and efficient web applications. Google themselves have used the tools for many of their popular web apps including Gmail and Google Maps. There are three parts to the Closure Tools: Closure Library, Closure Templates and Closure Compiler.

The Closure Library provides functions common tasks such as DOM manipulation and AJAX, similar to jQuery and Prototype, as well as UI elements such as a colour picker and a date picker. Closure Templates are a templating system used to build DOM elements.

The Closure Compiler is probably the most interesting tool in the Closure Tools. It minifies code and can perform complex analysis of the code to remove dead code and rewrite code into a more compact and efficient form. Here is a demonstration of the closure compiler.

Back End

The backend will use PHP and MySQL since they are powerful, popular and familiar to me. The back end will be a RESTful web service. A RESTful service constitutes a number of things but the only aspect of interest is the client-server separation. The client sends requests that act on a resource to the server, e.g. ‘remove project book 14305′, and the server processes. The client isn’t concerned by data storage or the process of retrieving the resource and the server isn’t concerned by how the resource is displayed. This style allows multiple different clients, e.g. web app client or and Android app client, to access the resources through the same service.