Enhancing the Database Schemas a little

I'm back with an update to yesterday's blog post. After giving it some more thought, I decided to refactor the database tables to better reflect more variety in the organizational structures and to allow for practical examples of self-joins. While this isn't a real-world database design, it will be an excellent foundation for demonstrating CRUD (Create, Read, Update, Delete) operations in future examples.

To recap, the schema I created is called 'johndemo'. After the recent changes, this schema now consists of eight interconnected tables: person, team, group, person_group, team_group, team_type, org_unit, and lob.

1. Person Table: This table records essential information about individuals, such as first and last names, and the team they belong to. Each person is assigned a unique ID and can only be a member of one team at a time.

2. Team_Type Table: This table enables the creation of Team Types, categorizing teams for easy organization.

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

4. Person_Team Table: A junction table that connects the person and team tables, tracking the many-to-many relationship between persons and teams, and allowing individuals to be associated with multiple teams simultaneously.

5. Group Table: Designed to record the names of different collections of persons. While a person can only belong to one team, they can be part of multiple groups, offering varied and dynamic relationships between individuals. This table will help demonstrate many-to-many relationships.

6. Person_Group Table: Another junction table connecting the person and group tables, tracking the many-to-many relationship between persons and groups, and allowing individuals to be associated with multiple groups at once.

7. Org_Unit Table: Represents a collection of people working together, containing a unique ID, Org Unit name, description, the person responsible for the team, and the parent org unit to which an org unit reports. This structure maintains an organized system for structuring an organization's hierarchy. In the future, this may be changed to represent a collection of job positions working together.

8. Lob Table: Short for Line of Business, this term is used for a group of related products or services within a business.

Like before, to help you visualize the schema, here's a graphical representation:

Below are the DDL scripts:

CREATE TABLE person (
  id int NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  active int NOT NULL DEFAULT '1',
  PRIMARY KEY (id)
)

CREATE TABLE team_type (
  id int NOT NULL,
  team_type_name varchar(45) NOT NULL,
  team_type_desc varchar(300) DEFAULT NULL,
  PRIMARY KEY (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,
  team_type_id int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_team_level (level),
  KEY idx_team_team_type_id (team_type_id),
  CONSTRAINT fk_team_team_type FOREIGN KEY (team_type_id) REFERENCES team_type (id)
)

CREATE TABLE person_team (
  person_team_id int NOT NULL AUTO_INCREMENT,
  person_id int NOT NULL,
  team_id int NOT NULL,
  join_date datetime DEFAULT NULL,
  PRIMARY KEY (person_team_id),
  KEY idx_person_team_person (person_id),
  KEY idx_person_team_team (team_id),
  CONSTRAINT fk_person_team_person FOREIGN KEY (person_id) REFERENCES person (id),
  CONSTRAINT fk_person_team_team FOREIGN KEY (team_id) REFERENCES team (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_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_person_group_group FOREIGN KEY (group_id) REFERENCES group (id),
  CONSTRAINT fk_person_group_person FOREIGN KEY (person_id) REFERENCES person (id)
)

CREATE TABLE org_unit (
  id int NOT NULL AUTO_INCREMENT,
  org_unit_name varchar(100) NOT NULL,
  org_unit_desc varchar(300) DEFAULT NULL,
  org_unit_parent_id int DEFAULT NULL,
  org_head_id int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_org_head_id (org_head_id),
  KEY idx_org_unit_parent_id (org_unit_parent_id),
  KEY fk_org_unit_org_unit_idx (org_unit_parent_id),
  CONSTRAINT fk_org_unit_org_unit FOREIGN KEY (org_unit_parent_id) REFERENCES org_unit (id),
  CONSTRAINT fk_org_unit_person FOREIGN KEY (org_head_id) REFERENCES person (id)
)

CREATE TABLE lob (
  id int NOT NULL AUTO_INCREMENT,
  lob_name varchar(45) NOT NULL,
  lob_desc varchar(300) DEFAULT NULL,
  lob_head_id int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fk_lob_head_idx (lob_head_id),
  CONSTRAINT fk_lob_head FOREIGN KEY (lob_head_id) REFERENCES person (id)
)

As mentioned earlier, there is a possibility that I may continue to refactor the schema as my blog examples progress.