Demo Relational Database Design for Photos Sharing Website from Scratch

Rapid advancement of technologies has led to information explosion. We are leaving our ‘footprint’ for all interactions done online. Surging availability of information creates challenges in data storage and management, and thought of searching the values of data. Let’s demonstrate designing process of a simple database for a website.

Relational database is a better option than spreadsheet to work with huge dimensions data. We might be facing replication, redundancy and inconsistency with spreadsheet. A systematic data storage allows more efficient and effective information management and retrieving process as compared to manual operation on spreadsheet. (Imagine a dataset with thousands of columns.) We are working to design a relational database that organize data in tables and is able to link to other tables by applying data modeling technique, ER modeling through a series of steps, conceptual, logical and physical data models. Let’s understand some simple terms for ER modeling:

Entity: Objects/ Components of data to be stored.

Attribute: Property or characteristics of entity.

Relation: Link or association of entities with each other.

Each row and column represent record and attribute respectively. The design process demonstrated is summarized into four steps:

1. Requirement analysis.
2. ER diagram.
3. Relational model mapping.
4. Integrity and Normalization Check.
5. SQL physical design.

Step 1: Problem Statement and Requirement Analysis

Our aim is to design a database that support some simple core functionality of a photo sharing website. A user can register an account at the site with limited free storage. A range of actions available to users are photos uploading, photo collections managing, users, groups, discussion and ratings, etc.

Assuming we only concern about registered users and their associated people visiting the site. Other unrelated visitors can only view photos available publicly. Each entity has set of attributes as shown in the first ER Diagram. Some information on relation is outlined below:

• Each group created by a user can have a list of photos collections and discussion threads. Some users are member of groups.
• Only users can upload photos, rate and comments on photos.
• Some comments are in the threads.
• A collection is created by user and has a key photo. Photos can have tags, ratings and comments.

Step 2: Drawing ER Diagram

Trying to draw the diagram separately to make it clearer at a glance, rather than fitting everything including entities, attributes, relationships, cardinality and participation into single diagram which might be messy. Cardinality is similar to function mapping in math. There are two forms of participation, total (aka mandatory) participation which subjects in an entity set must participate in the relation instance, and partial (aka optional) participation is when some but not all subjects in the set is participating in the relation.

ER Diagram for Entities.

ER Diagram between entities, outlining the relationships and cardinality.

Steps 3: ER Model - Relational Model Mapping

Relational Model with relationships and degree of participation.

Step 4: Integrity and Normalization Checking

Entity Integrity: The primary key cannot contain NULL, it must be unique for each record.

Referential Integrity Rule: Foreign key values must match primary key in table referenced.

Anomaly if integrity violated:

• Problem with modification or update to tables. Eg, a record can only be inserted into a table if there is no replicated primary key or foreign key does not exist in parent table.
• If primary key value in parent table is updated and it is referenced elsewhere, we can disable changes, cascade changes to other related tables or set foreign keys to be NULL.

Domain Integrity: Specified set of accepted values and constraints (if needed) for attributes, ensuring consistency in records.

Normalization: Decomposing larger tables into smaller relations logically, with no loss of information and no redundant data.

Eg 1: ‘Name’ is composite attribute. It violated first normal form (1NF). Attribute ‘name’ is separated into two columns, surname and given name. Hence the attributes have atomic values.

Eg 2: Assuming

Photo : {id, title, collection, owner, technical, visibility, safety level, upload date}

Each photo can be in multiple collections, but only one owner. Non key attribute such as ‘owner’ does not depend on ‘photo id’, not entire set of primary key (photo id, collection id). It violated 2NF. The table is further decomposed to attain higher level normal form.

Photo : {id, title, owner, technical, visibility, safety level, upload date} (3NF)

Collection: {photo id, collection id}

Step 5: SQL Physical Design.

Code shared for this section is built on postregSQL server on pgAdmin4, there are some differences in syntax for different server chosen. For example, ‘CREATE DOMAIN …’ implemented is invalid for MySQL server. MySQL Workbrench is a nice tool for database designing with forward and reverse engineering functions which is definitely helpful for transformation between SQL scripts and ER Models. This is a rather simple demo for data modeling, task will get more complicated as dimensions of information increases, more relationships and functionality aspects to be taken care of.