Now Available On Github!

In the first part of the tutorial, I basically laid out the vision of the project. It’s time to setup a few files and directories to make things easier on us. I’m working entirely through Netbeans (as part of my “try out every IDE there is” project) which manages my FTP and everything else. There’s a certain logic to it so I’ll try to follow it. Here’s the breakdown:

- BookBlog
admin (for administration/back-end interface, this part will differ in styling and routing from the original project)
classes (PHP classes. I’ll try to implement some kind of autoloader to get all these included)
css (holds all of my css including LESS files before compiling)
— style.less (will compile to .css and has includes for all of the necessary files)
— style.css (compiled above file)
less (less directory)
—- main.less (main less file compiles all of the rest of the files)
—- base.less (will include normalize.css or a reset file plus baseline)
js (holds all of JS. These will be either minified and loaded together or loaded via head.js)
— jQuery
— bb-base.js (base javascript for BookBlog, will include basic functionality, procedures etc.)
sql (holds SQL statements. I’m putting these outside of PHP files for easier management)
— install.sql (install file, contains database structure and initial data)
— template (empty right now, will contain themes and templates)
— header.php (base header that includes html header info, includes, and javascript)
— index.php (main controller file)
— index.html (mock up layout)
— page.php (controller file for pages and page logic, may get moved into a templates directory)
— page.html (mock up layout)
— static.php (for static pages such as contact page, about page etc. I may move this into a templates directory)
— static.html (mock up layout)
— table-of-contents.php (table of contents will show up here)

The structure will change as time goes on but this will work for now. I’ll get rid of the mock-ups once they are fully implemented.

SQL and Tables

Let’s worry about setting up the database later, and look directly at the tables. What are the fields we need? What are the different tables necessary? How will they relate? I think we’ll need to start off with some basic tables. Here’s the breakdown:

  • user table- will we have multiple users? idk. But let’s include it to make this future-proof. This will include all of the author/authors info such as username/password/userid
  • user info table – relational. This will be structured like so: userid (to relate to our last table)/key/value. This way we can add as many fields as we need such as “description – description text”, “email – email value” and so on.
  • settings table – general settings based on the key-value pairing. This stuff is global so that’s why it’s separate from user info.
  • taxonomy – basic taxonomy description and general info such as how it’s supposed to be used, store, descriptions and so on. This is mainly for personal help.
  •  bookdata- this will be broken down to the last movable part. If it’s a “section” for example, you’ll have: section# / parent (Chapter III) / section position (3rd section for example) / revision/ blurb / custom code in head (consisting of the text included)

The last one may not make perfect sense, I’m still working out the logic. I’d like to keep text by section so that it can easily be reflowed. The issue here is the reflowing itself. I’ll have to somehow split the text into pages. Having to pull the entire thing and then break it down may be taxing and costly yet, I’m not sure how else to go about this.

User Table

Let’s start with the easy one that may or may not be used much. It will store our user, userid, password, and admin boolean (true of admin, false if not). It will be used for quick access. Let’s create the SQL. We’ll later wrap it in PHP and create an install script:

CREATE TABLE users (

id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
username VARCHAR(30), 
password VARCHAR (255),
adminstatus  INT

);

INSERT INTO users (username, password, adminstatus)
VALUES ("admin", "secured password", 1);

w00t! Done. Oh yeah, we gotta add some security to the password. Since I want this table to have 3-5 users max, there’s no need for some crazy security so we’ll use a SHA-1 encryption, some customized salt… more on that later. :)

Userinfo

Let’s make our userinfo table! This one will relate to the original so we gotta make a connecting column.

CREATE TABLE userinfo (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
userid INT,
infokey VARCHAR(255),
infovalue BLOB
);

INSERT INTO userinfo (userid, infokey, infovalue)
VALUES (1, "Name" , "Antonin Januska");

Cool, the first column connects by the user ID (not the username) so that if we change the username, all our connections stay in place. The value is set to blob (I may change this later) so it can hold an innumerable amount of data instead of a small piece of text. I’m making this future-proof because who knows, maybe I’ll want to put something pretty big in there (unlikely but who knows). I’ll see how I use this table and when I make my mind up, I’ll probably alter this (or push through via a revision or whatever).

Settings Table

This one is basically like the one above except without a user field. I might combine the two and assume that a row without the userid declared is a general setting. The thing is, for settings, you don’t need a blob, you need something smaller and faster.

/* SETTINGS */
CREATE TABLE settings (
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
settingkey VARCHAR(55), 
settingvalue VARCHAR(255),
description VARCHAR(255),
UNIQUE(settingkey)
);

INSERT INTO settings  (settingkey, settingvalue, description)
VALUES ("Active", 1 , "The value declares if the system is ready and active");

Basically the same with a description included. The unique specifier on the bottom basically tells MySQL not to allow adding of a row with the same key as another row. Which is good. This isn’t user-based so there shouldn’t be any duplicates. That could cause issues.

Taxonomy

This table is for semantics only. Basically, it helps describe what’s happening in our bookblog system.

/* TAXONOMY */
CREATE TABLE taxonomy (
id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(id),
tax_name VARCHAR(255), 
tax_parent VARCHAR(255),
description VARCHAR(255)
);

INSERT INTO  taxonomy (tax_name, tax_parent, description)
VALUES ("Act", null, "Top Level Taxonomy");

INSERT INTO  taxonomy (tax_name, tax_parent, description)
VALUES ("Chapter", 1, "Chapter");

INSERT INTO  taxonomy (tax_name, tax_parent, description)
VALUES ("Section", 2, "Smallest Section!");

Book Data

At first, I wanted to use a hierarchical or tree-like table with the ability to have infinite nodes and sub-sections but for that I’d need not only a Closure table but a hell of an SQL knowledge that I just don’t have right now. For future reference, what I just said is basically using a table that allows having an entire family and all their relationships mapped. Think of it this way. The table would have grandparents, matching parents, children, grandchildren, and so on. And having a “tree” structure, think of it as a FAMILY tree. And so with that structure, you can climb back to the grandparent, go to the parent, pick a child, and follow that route then backtrack and follow a different “branch” of the family. This sounds simple in theory but in practice, you’re facing either “recursive queries” wherein you have to ask each person in the family tree who their parent is until you get to a common ancestor that you wish to query or a sibling you wish to find out about, “common table expressions” (another way of handling recursive queries), using a Nested Set Model, or the aforementioned closure tables.

A closure table makes this easier by mapping EVERY individual and their relationships. For example, a grandparent will have many entries, linking it to every child/grand-child/etc. it has. If the grandparent has 3 children whom each have 5 children, we’ll have 16 records (one to link it to itself) each one detailing how far that child is from the grandparent. Is the child 2 or 3 generations away? The closure table helps out with this information.

. So instead, I decided to have this set-in-stone structure. Upon installation, I’m hoping to give the users several different choices.

CREATE TABLE bookdata(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    grandId INT,
    grandP INT,
    parenId INT,
    parenP INT,
    curPosition INT,
    data_name VARCHAR(255),
    data_text BLURB,
    data_css BLURB,
    data_js BLURB,
    data_teaser TINYBLURB
)

What this structure allows is quick traversing, updates, querying and so on. Basically if I want to query the first section of the third chapter of the fifth act, I don’t have to go through multiple sub-queries to find the right parents. I can simply query for a section that has a grandparent position of 5, parent position of 3, and current position of 1. Much easier. If I want to get the 2nd chapter of the 1st act, I can simply query for a null grandparent position, parent position of 1, and current position of 2. See? Much easier :)

I’m considering creating an extra meta-table that will store extraneous information. We’ll see if that’s necessary or if that will be a good choice for the 2.0 (someday!) version.

Sample Queries

Since I’ll be using these tables often, I wanted to create some sample ones or set-in-stone ones that I can reuse in the future. I’m planning to have a whole class system of these. How about retrieving some user data:

/* Get ALL user info */
SELECT * from users where username = '';

/* Retrieve user ID */
SELECT id from users where username = '';

/* retrieve user settings 
*/
SELECT infokey, infovalue 
FROM userinfo 
LEFT JOIN users
ON userinfo.userid = users.id
where users.username = '';

/* retrieve settings (global) */
SELECT settingkey, settingvalue from settings;

Pretty simple. We can get the ID, all user info, user settings, and global settings. The beauty of this database structure is that it doesn’t require heavy dependencies among tables and it’s extremely simplified and light-weight.

Let’s check out how we can tell a user what our structure is:

/* get taxonomy */
SELECT * from taxonomy;

Bam! Okay, this one is overly simplified but I still like having a separate table for just describing the structure of everything, even if it’s not tied to anything else. This information just doesn’t fall anywhere comfortable. It’s not a global setting, or a user setting, or a post itself. Speaking of posts and pages….

/* first section of the first chapter of the first act */
SELECT * FROM bookdata 
WHERE grandP = 1 AND parenP = 1 AND curPosition = 1;

/* how about all sections in the first chapter */
SELECT * FROM bookdata 
WHERE grandP = 1 AND parenP = 1 
ORDER BY curPosition ASC;

/* first act and chapter and first section*/
SELECT * FROM bookdata 
WHERE (grandP = null AND parenP = null AND curPosition = 1)
|| (grandP = null AND parenP = 1 AND curPosition = 1) 
|| (grandP = 1 AND parenP = 1 AND curPosition = 1);

Easy selections. We use the positioning system to make really great queries. Makes me think we don’t even need the “id” columns! :) (actually, maybe we don’t. I’ll revisit this later).

Conclusion

I hope this gives you some insight into how you can design custom tables and databases for projects and what kind of thinking follows creating these databases. The problem today, I think, is that we’re given solutions and materials to read but we’re not given the process of figuring it out.