Relational Databases

I have been using databases in projects, both personal and professional, for several years. My first introduction to databases was when I first started working on web development projects more than 8 years ago. As a PHP developer, I mostly used MySQL. In 2012, I joined Wyle CAS Group as an intern and later part-timer software developer, where I first interacted with Microsoft SQL Server, which I wrote software to connect to. I've also used SQLite to application configuration data.

Below is an example of one of the more complex SQL queries that I've written. This was used by an old PHP content management system project of mine to select all information associated with a user from the database using only their session cookie. Database::table() is used to fetch a table name, and the question marks represent an area where the database library (PHP's PDO, in this case) is to insert sanitized input.

$query = "SELECT ".

	// Select the session information from the database
	"session.session_id AS session_id, ".
	"session.session_support AS session_support, ".

	// Select the user information from the database
	"user.user_id AS user_id, ".
	"user.user_email AS user_email, ".
	"user.user_superadmin AS user_superadmin, ".
	"user.user_name AS user_name, ".

	// Select the tracking information from the database
	"GROUP_CONCAT(DISTINCT userip.ip_address) AS user_ips, ".
	"GROUP_CONCAT(DISTINCT usercookie.cookie_value) AS user_cookies, ".
	"GROUP_CONCAT(DISTINCT useragent.agent_string) AS user_agents, ".

	// Select the user group information from the database
	"GROUP_CONCAT(DISTINCT usergroup.group_id) AS group_ids, ".
	"GROUP_CONCAT(DISTINCT usergroup.group_name) AS group_names, ".
	"GROUP_CONCAT(DISTINCT permission_key_allowed.permission_key_name) AS permissions_allowed, ".
	"GROUP_CONCAT(DISTINCT permission_key_denied.permission_key_name) AS permissions_denied, ".
	"GROUP_CONCAT(DISTINCT permission_key_never.permission_key_name) AS permissions_never ".

	"FROM ".Database::table("session")." AS session ".

	// Join the user from the database
	"LEFT JOIN ".Database::table("user")." AS user ON session.user_id = user.user_id ".

	// Join tracking information from the database
	"LEFT JOIN ".Database::table("useragent")." AS useragent ON useragent.user_id = user.user_id ".
	"LEFT JOIN ".Database::table("userip")." AS userip ON userip.user_id = user.user_id ".
	"LEFT JOIN ".Database::table("usercookie")." AS usercookie ON usercookie.user_id = user.user_id ".

	// Join group information and assignments for this user
	"INNER JOIN ".Database::table("usergroup_assignment")." AS group_assignment ON group_assignment.assignment_user_id = user.user_id AND group_assignment.assignment_active = 1 ".
	"INNER JOIN ".Database::table("usergroup")." AS usergroup ON usergroup.group_id = group_assignment.assignment_group_id AND group_assignment.assignment_user_id = user.user_id ".

	// Join the permission keys for this user and their assigned groups
	"LEFT JOIN ".Database::table("permission_assignment")." AS permission_assignment ON usergroup.group_id = permission_assignment.permission_assignment_groupid OR user.user_id = permission_assignment.permission_assignment_userid ".
	"LEFT JOIN ".Database::table("permission_key")." AS permission_key_allowed ON permission_assignment.permission_assignment_key = permission_key_allowed.permission_key_id AND permission_assignment.permission_assignment_allow = 1 ".
	"LEFT JOIN ".Database::table("permission_key")." AS permission_key_denied ON permission_assignment.permission_assignment_key = permission_key_denied.permission_key_id AND permission_assignment.permission_assignment_deny = 1 ".
	"LEFT JOIN ".Database::table("permission_key")." AS permission_key_never ON permission_assignment.permission_assignment_key = permission_key_never.permission_key_id AND permission_assignment.permission_assignment_never = 1 ".

	// Conditions
	"WHERE session.session_id = ? AND session.session_cookie = ?";

Below is a screenshot of phpMyAdmin on my Macbook's local development server (which uses MAMP Pro). The database shown is used by one of the projects discussed in my portfolio: TicketAngel. I, along with my teammate Jesse Moore, designed the SQL schema for that project.


I do not have practical experience using NoSQL, although I understand the basic concepts behind it, and would be fascinated to find an opportunity to use it in a professional context.