Target Audience

Tasked with creating a functional website featuring multiple expandable databases, it made sense for a target audience to be a student organization. With the availability of several friends in Cornell Alpha Phi willing to give me information, resources (such as names and pictures), and user test participants, I chose the members of this group as my target audience. Additionally, with multiple groups and titles within the organization, such as lineages and leadership roles, it is a logical audience for a website with several interacting databases. With this database-heavy website, new members can see previous members of their lineage, and older members can see more recent additions to their lineage as well as any leadership positions or pictures of them.

Site Organization

Based on the needs of the sorority, the information about the group I had, and wanting to work with multiple interacting databases, I knew early on what the three main pieces of content would be for this product: the database of members/sisters, a lineage database, and an images gallery/database. Additionally, each database would need a way for a user to add to the database. Knowing these pieces of content then allowed me to perform a card sorting procedure to determine the website's architecture.

I started by dividing this set of content into two groups based on the actions that users would partake in with each - one tab titled "View" and the other titled "Add." This card sort is shown below.

However, after thinking about this organization, I believed it would not be the best option. The most problematic would have been that this organization would dump all content onto only two pages, cluttering each page too much. I then came up with the below organization, which would mitigate this issue. When hovering over "View" and "Add," the appropriate options would appear as a dropdown menu to view pages to either view or add "Sister(s)," "Lineage(s)," or "Image(s)."

Moving away from this first type of navigation to consider different methods of organization, I expanded on the navigation organization to the two final card sorts (below), which I decided between.

Since most, if not all, image galleries place the images with the button to add photos to this gallery, I decided to move forward with the organization in the fourth card sort (the picture on the right above) to keep the image gallery and upload image features together on the same page. With this organization, each category of content (Sisters, Lineages, and Images) will have its own page, with viewing and adding features for each on the same page.

Sketches

I iterated several times on designing and sketching the website's pages but eventually ended up on these final sketches.

The Sisters page, shown below, includes the ability to search the page by multiple criteria, view the table displaying the Alpha Phi sisters database, and fill out a form to instantly add an entry to the database.

The Lineages page, shown below, includes the ability for the user to search the page based on member name or lineage name, view the table displaying the database of Alpha Phi lineages, and fill out a form to add an entry to the database instantly.

In the final sketch of the Image gallery page, I included a button titled "Filter by Tags." When a user clicks this button, all existing tags appear, with the ability to click on them and be shown the images with that particular tag. Besides this filter function, users can also simply see all photos in the gallery, arranged in reverse chronological order.

For the image details part of the images page, clicking on the gallery image directs to another page entirely, which I modified from the initial sketch, with an enlarged image, tags, source, and a menu button. I added a caption to the image details page and a "Add a Tag" and "Delete a Tag" to the menu hovering dropdown.

As for the Add Image page, this page opens when a user clicks on the + button on the main images page. As I added some information to the image details page, I added some form inputs to gather this information, such as caption, year taken, source name, and source URL. Other than these content additions, the design of the upload image page is the same as the initial design.

The design rationale for my website comes from best practices and existing design patterns in other technologies.

Images will be sorted and grouped in reverse chronological order, similar to Google Photos' image gallery design.
All images will appear as a square, equal in dimensions and size to each other, to serve as a thumbnail in the gallery, similarly to Instagram.
Each square image in the gallery will have the same padding between each picture, and five squares will occupy each row, creating a uniform layout like many existing photo galleries.
The Images page will feature a '+' button for users to add pictures, as this symbol is fairly universal for this functionality on other image display sites and other types of products.

Database Planning & Development

Before coding anything, I started by understanding the database schema and query plan for this site's content. Below is a series of these pseudocode chunks to describe the format of the databases as well as their constraints, and their exact code counterparts that made up the actual databases within the init.sql file. The first three blocks are of the three main databases, titled "sisters," "lineages," and "images." In the third table, titled "images," I realized after initializing the database that I needed to add two fields, resulting in the "ALTER" statements.

sisters (
id : INTEGER {Primary Key, Unique, Not Null, Auto-Increment}
first_name : TEXT {Not}
last_name : TEXT {Not}
class_year : INTEGER {Not}
big_sister : TEXT
little_sister : TEXT
position : TEXT
)

CREATE TABLE sisters (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
class_year INTEGER NOT NULL,
big_sister TEXT,
little_sister TEXT,
position TEXT
);

lineages (
id : INTEGER {Primary Key, Unique, Not Null, Auto-Increment}
lineage_name : TEXT {U}
2010_sister_first_name : TEXT
2010_sister_last_name : TEXT
2011_sister_first_name : TEXT
2011_sister_last_name : TEXT
2012_sister_first_name : TEXT
2012_sister_last_name : TEXT
2013_sister_first_name : TEXT
2013_sister_last_name : TEXT
2014_sister_first_name : TEXT
2014_sister_last_name : TEXT
2015_sister_first_name : TEXT
2015_sister_last_name : TEXT
2016_sister_first_name : TEXT
2016_sister_last_name : TEXT
2017_sister_first_name : TEXT
2017_sister_last_name : TEXT
2018_sister_first_name : TEXT
2018_sister_last_name : TEXT
2019_sister_first_name : TEXT
2019_sister_last_name : TEXT
2020_sister_first_name : TEXT
2020_sister_last_name : TEXT
)

CREATE TABLE lineages (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
lineage_name TEXT UNIQUE,
sister_first_name_2010 TEXT,
sister_last_name_2010 TEXT,
sister_first_name_2011 TEXT,
sister_last_name_2011 TEXT,
sister_first_name_2012 TEXT,
sister_last_name_2012 TEXT,
sister_first_name_2013 TEXT,
sister_last_name_2013 TEXT,
sister_first_name_2014 TEXT,
sister_last_name_2014 TEXT,
sister_first_name_2015 TEXT,
sister_last_name_2015 TEXT,
sister_first_name_2016 TEXT,
sister_last_name_2016 TEXT,
sister_first_name_2017 TEXT,
sister_last_name_2017 TEXT,
sister_first_name_2018 TEXT,
sister_last_name_2018 TEXT,
sister_first_name_2019 TEXT,
sister_last_name_2019 TEXT,
sister_first_name_2020 TEXT,
sister_last_name_2020 TEXT
);

images (
id : INTEGER {Primary Key, Unique, Not Null, Auto-Increment}
file_name : TEXT {Not Null}
file_ext : TEXT {Not Null}
year_taken : INT {Not Null}
description : TEXT
source_name: TEXT
source_URL: TEXT
)

CREATE TABLE images (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
file_name TEXT NOT NULL,
file_ext TEXT NOT NULL,
year_taken INTEGER NOT NULL,
description TEXT
);
‍
ALTER TABLE images
ADD source_name TEXT;
‍
ALTER TABLE images
ADD source_url TEXT;

After these three main databases, I also needed to plan and program the database for the tagging feature of the Images page.

tags (
id : INTEGER {Primary Key, Unique, Not Null, Auto-Increment}
tag_name : TEXT {Not Null}
)

CREATE TABLE tags (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
tag_name TEXT NOT NULL
);

Lastly, I had to create the database to link the "images" and the "tags" database that would allow users to browse and filter images by tags. I was able to achieve this by linking an image's id with a tag's id, within their respective databases, in this new database titled "image_tags."

image_tags (
id : INTEGER {Primary Key, Unique, Not Null, Auto-Increment}
image_id : INTEGER {Not Null}
tag_id : INTEGER {Not Null}
)

CREATE TABLE image_tags (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
image_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL
);

After initializing each database in init.sql, I populated it with relevant seed data to begin the website content. With the beginnings of each database now in place, I began to think of the functionality I wished to implement, many of which would require requests and SQL queries. For each of these tasks, such as viewing the images in the gallery, searching for a member based on a specific descriptor, and more, I thought about the type of request it would need (GET or POST), the parameters it would require, and the applicable SQL query as necessary. Lastly, I made an outline for how to tackle the remaining code for the project: the PHP, HTML, and CSS.

For reasons of privacy towards the people on the website, as well as concerns of academic integrity, I cannot publish any substantial amounts of code on this public website. If you wish to see more of the code for this project, please feel free to contact me!

Final Product

Below is a walkthrough of the different tasks associated with the website. I will blur personal identifying content out of these videos for privacy reasons.

Final Thoughts

Due to the timing of this project, in the spring of 2020, this project's circumstances taught me valuable lessons about remote working and self-sufficiency in terms of debugging and the use of documentation. As my first taste of remote learning, this project taught me how to know when to take a break from a specific aspect of a project and come back to it later, a problem I believe many developers often face in their work. Additionally, I learned the basics, importance, and practical skills of debugging through this project that would stick with me for the rest of my college career and beyond. Lastly, it taught me the place of documentation within a programmer's life and how to utilize it to its fullest extent. These lessons and skills that I took from this project, while challenging to learn at the time, made me a better developer forever.