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.