Sample Database tables for future demos

Today, I wanted to create a set of database tables, specifically designed to serve as a foundation for demonstrating CRUD (Create, Read, Update, Delete) operations in future examples. This practical guide will not only help me better explain the relationships between various database entities but also improve my ability to design simple table relationships.

To kick things off, I opened up my trusty MySQL workbench and started by creating a new schema called 'johndemo'. This schema comprises five interconnected tables: person, team, division, group, and person_group.

Person Table: This table is designed to record essential information about individuals, but to keep things simple I only have their first and last names, as well as the team they belong to. Each person is assigned a unique ID and can only be a member of one team at a time.

Team Table: The team table stores details about various teams, such as a unique ID, name, and description. Each team is also assigned a level from 1-10, indicating its hierarchy or skill level. A team can only be associated with a single division.

Division Table: A division represents a collection of teams, and its table contains a unique ID, division name, and description. This simple structure helps to maintain an organized system for grouping teams together.

Group Table: The group table is designed to record the names of different collections of persons. While a person can only belong to one team, they may be part of multiple groups. This flexibility allows for more varied and dynamic relationships between individuals and will be used to help demonstrate many-to-many relationships.

Person_Group Table: This is a junction table that connects the person and group tables. It's used to track the many-to-many relationship between persons and groups, allowing individuals to be associated with multiple groups at once.

To help you visualize the schema, here's a graphical representation:

Below are some DDL scripts to create the tables:

CREATE TABLE division (
  id int NOT NULL AUTO_INCREMENT,
  division_name varchar(45) NOT NULL,
  division_desc varchar(300) DEFAULT NULL,
  PRIMARY KEY (id)
)

CREATE TABLE 'group' (
  id int NOT NULL AUTO_INCREMENT,
  group_name varchar(45) NOT NULL,
  group_desc varchar(300) DEFAULT NULL,
  PRIMARY KEY (id)
)

CREATE TABLE person (
  id int NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  team_id int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_person_team_id (team_id),
  CONSTRAINT fk_person_team FOREIGN KEY (team_id) REFERENCES team (id)
)

CREATE TABLE team (
  id int NOT NULL AUTO_INCREMENT,
  team_name varchar(45) NOT NULL,
  level int NOT NULL,
  team_desc varchar(300) DEFAULT NULL,
  div_id int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_team_div_id (div_id),
  KEY idx_team_level (level),
  CONSTRAINT fk_team_division FOREIGN KEY (div_id) REFERENCES division (id)
)

CREATE TABLE person_group (
  person_group_id int NOT NULL AUTO_INCREMENT,
  person_id int NOT NULL,
  group_id int NOT NULL,
  registered_date datetime DEFAULT NULL,
  hours_committed int DEFAULT NULL,
  PRIMARY KEY (person_group_id),
  KEY fk_person_idx (person_id),
  KEY fk_group_idx (group_id),
  CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES 'group' (id),
  CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES person (`id`)
)

It is very likely, that I may be adding more tables and columns as needed for forthcoming demos.

In conclusion, this simple database schema provides a foundation that I can build on for demonstrating CRUD operations in future examples. The tables are designed to accommodate a variety of relationships between persons, teams, divisions, and groups, making the schema adaptable to various scenarios. I hope this will prove to be a useful resource in future posts.