1
\$\begingroup\$

I have a small SQL server database and VBA program that is used to automate tasks related to tennis students, programs, practices, performances, and student testing.

I'm trying to learn db design and want to redesign so it's more normalized and correct, so looking for suggestions on the best db design, especially regarding the "time" entities (program, session, week, season, and practice days). I will be developing dashboards in Power BI, so I initially thought a Date table would reside there, but maybe I need it in source db...

Main questions::

  • Do I need more entities? Season, Week, Date tables?
  • How is the best way to normalize the student_rank data? Do I need a Class/Practice table or can I just use the Rank entity (since only rankings are recorded at a practice)?

Below are the details (split into 4 sections) to explain the requirements and replicate the current environment.

Details

I. Business rules:

  • There are multiple programs (beginner, intermediate, advanced) offered in a season (and only 1 coach for this purposes).
  • There are 4 sessions in each season (could change) and so each session has many programs.
  • Each session is divided into 8 weeks (but this could change in future).
  • Programs share the same season, session, and week numbers (meaning they are all going on in the same time frame). But each program has a different and number of predefined practice days and times.
  • Some weeks during the season are off (for Christmas holidays, Spring break, etc). The scheduled practice class day and times for each program/session/week are predetermined at the start of each season.
  • There is a predefined “roster” of students that attend certain days, however students are allowed to switch up their practice days. For example, program A has 3 practice days a week on Tuesdays and Thursdays, and Saturdays. Student A is enrolled in the Tuesday and Thursday classes, but if they miss a Thursday class in a particular week, they can attend the Saturday class to makeup for missing Thursday.
  • A practice and class is synonymous.
  • The only metric recorded during a practice is each student's ranking for that day compared to the other students in the same practice class.
  • A student participates in only one program at a time but can participate in multiple programs over sessions or seasons.

II. Reporting requirements:

  • A student's performance/Ranks are recorded in each practice class (each student is ranked against other students in the same practice class).
  • A report is run that is basically a ladder ranking for the whole class that is run each week.

In the last week of each session, two events happen:

  1. The top 4 ranked on the ladder have a playoff to determine the "session winner".
  2. Each student is given test of various on court categories and objectives.

III. High level design [1]: https://i.sstatic.net/yfbGs10w.png

IV. DDL that is currently designed:

use master
GO
-- create database dtltennis;
drop database if exists JBtennis
GO

create database JBtennis
GO
--use dtltennis;
use JBtennis
GO

create schema NRS
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object:  Table [NRS].[proficiency_category]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[proficiency_category](
    [proficiency_category_id] [int] NOT NULL,
    [shot_category] [varchar](50) NOT NULL,
    [shot_category_desc] [varchar](200) NULL,
 CONSTRAINT [pk_proficiency_category_id] PRIMARY KEY CLUSTERED 
(
    [proficiency_category_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[proficiency_objective]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[proficiency_objective](
    [proficiency_objective_id] [int] NOT NULL,
    [proficiency_category_id] [int] NOT NULL,
    [shot_objective] [varchar](200) NULL,
    [shot_objective_desc] [varchar](200) NULL,
    [score_attempts] [smallint] NULL,
 CONSTRAINT [pk_proficiency_objective_id] PRIMARY KEY CLUSTERED 
(
    [proficiency_objective_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[proficiency_test]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[proficiency_test](
    [proficiency_test_id] [int] IDENTITY(1,1) NOT NULL,
    [student_id] [int] NOT NULL,
    [program_id] [int] NOT NULL,
    [program_session_id] [int] NOT NULL,
    [proficiency_objective_id] [int] NOT NULL,
    [score] [smallint] NULL,
 CONSTRAINT [pk_proficiency_test] PRIMARY KEY CLUSTERED 
(
    [proficiency_test_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[proficiency_test_template]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[proficiency_test_template](
    [proficiency_test_template_id] [int] IDENTITY(1,1) NOT NULL,
    [template_num] [int] NOT NULL,
    [proficiency_objective_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [proficiency_test_template_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[proficiency_test_template_session]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[proficiency_test_template_session](
    [proficiency_test_template_session_id] [int] IDENTITY(1,1) NOT NULL,
    [template_num] [int] NOT NULL,
    [program_session_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [proficiency_test_template_session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[program]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[program](
    [program_id] [int] IDENTITY(1,1) NOT NULL,
    [program_desc] [varchar](50) NOT NULL,
 CONSTRAINT [pk_program_id] PRIMARY KEY CLUSTERED 
(
    [program_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[program_session]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[program_session](
    [program_session_id] [int] IDENTITY(1,1) NOT NULL,
    [program_id] [int] NOT NULL,
    [session_desc] [varchar](50) NOT NULL,
    [season] [varchar](50) NOT NULL,
    [session_start_date] [smalldatetime] NOT NULL,
    [session_end_date] [smalldatetime] NOT NULL,
 CONSTRAINT [pk_program_session_id] PRIMARY KEY CLUSTERED 
(
    [program_session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[student]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[student](
    [student_id] [int] IDENTITY(1,1) NOT NULL,
    [first_name] [varchar](50) NULL,
    [last_name] [varchar](50) NULL,
    [full_name] [varchar](101) NULL,
    [phone] [varchar](50) NULL,
    [email] [varchar](50) NULL,
    [DOB] [date] NULL,
    [utr] [int] NULL,
    [plays_lr] [char](1) NULL,
    [parent_name] [varchar](100) NULL,
    [parent_phone] [varchar](50) NULL,
    [parent_email] [varchar](50) NULL,
    [note] [varchar](8000) NULL,
    [is_active] [char](1) NULL,
 CONSTRAINT [pk_student_id] PRIMARY KEY CLUSTERED 
(
    [student_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [NRS].[student_rank]    Script Date: 5/16/2024 6:10:06 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [NRS].[student_rank](
    [student_rank_id] [int] IDENTITY(1,1) NOT NULL,
    [student_id] [int] NOT NULL,
    [program_id] [int] NOT NULL,
    [date] [date] NOT NULL,
    [program_session_id] [int] NOT NULL,
    [week_id] [int] NOT NULL,
    [day_id] [int] NOT NULL,
    [day_desc] [varchar](10) NOT NULL,
    [rank] [tinyint] NULL,
    [rank_denom] [tinyint] NULL,
    [absent] [tinyint] NULL,
    [rained_out] [tinyint] NULL,
 CONSTRAINT [pk_student_rank] PRIMARY KEY CLUSTERED 
(
    [student_rank_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Index [uq_objective_score]    Script Date: 5/16/2024 6:10:06 PM ******/
ALTER TABLE [NRS].[proficiency_objective] ADD  CONSTRAINT [uq_objective_score] UNIQUE NONCLUSTERED 
(
    [shot_objective] ASC,
    [score_attempts] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [uq_proficiency_test]    Script Date: 5/16/2024 6:10:06 PM ******/
ALTER TABLE [NRS].[proficiency_test] ADD  CONSTRAINT [uq_proficiency_test] UNIQUE NONCLUSTERED 
(
    [student_id] ASC,
    [program_id] ASC,
    [program_session_id] ASC,
    [proficiency_objective_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [uq_template_session_program_session_template]    Script Date: 5/16/2024 6:10:06 PM ******/
ALTER TABLE [NRS].[proficiency_test_template_session] ADD  CONSTRAINT [uq_template_session_program_session_template] UNIQUE NONCLUSTERED 
(
    [template_num] ASC,
    [program_session_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [uq_program_session]    Script Date: 5/16/2024 6:10:06 PM ******/
ALTER TABLE [NRS].[program_session] ADD  CONSTRAINT [uq_program_session] UNIQUE NONCLUSTERED 
(
    [program_id] ASC,
    [session_desc] ASC,
    [season] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Index [uq_student_rank]    Script Date: 5/16/2024 6:10:06 PM ******/
ALTER TABLE [NRS].[student_rank] ADD  CONSTRAINT [uq_student_rank] UNIQUE NONCLUSTERED 
(
    [student_id] ASC,
    [program_session_id] ASC,
    [week_id] ASC,
    [day_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [NRS].[proficiency_objective]  WITH CHECK ADD  CONSTRAINT [fk_objective_category_id] FOREIGN KEY([proficiency_category_id])
REFERENCES [NRS].[proficiency_category] ([proficiency_category_id])
GO
ALTER TABLE [NRS].[proficiency_objective] CHECK CONSTRAINT [fk_objective_category_id]
GO
ALTER TABLE [NRS].[proficiency_test]  WITH CHECK ADD  CONSTRAINT [fk_proficiency_test_proficiency_objective_id] FOREIGN KEY([proficiency_objective_id])
REFERENCES [NRS].[proficiency_objective] ([proficiency_objective_id])
GO
ALTER TABLE [NRS].[proficiency_test] CHECK CONSTRAINT [fk_proficiency_test_proficiency_objective_id]
GO
ALTER TABLE [NRS].[proficiency_test]  WITH CHECK ADD  CONSTRAINT [fk_proficiency_test_program_id] FOREIGN KEY([program_id])
REFERENCES [NRS].[program] ([program_id])
GO
ALTER TABLE [NRS].[proficiency_test] CHECK CONSTRAINT [fk_proficiency_test_program_id]
GO
ALTER TABLE [NRS].[proficiency_test]  WITH CHECK ADD  CONSTRAINT [fk_proficiency_test_program_session_id] FOREIGN KEY([program_session_id])
REFERENCES [NRS].[program_session] ([program_session_id])
GO
ALTER TABLE [NRS].[proficiency_test] CHECK CONSTRAINT [fk_proficiency_test_program_session_id]
GO
ALTER TABLE [NRS].[proficiency_test]  WITH CHECK ADD  CONSTRAINT [fk_proficiency_test_student_id] FOREIGN KEY([student_id])
REFERENCES [NRS].[student] ([student_id])
GO
ALTER TABLE [NRS].[proficiency_test] CHECK CONSTRAINT [fk_proficiency_test_student_id]
GO
ALTER TABLE [NRS].[proficiency_test_template]  WITH CHECK ADD  CONSTRAINT [fk_proficiency_test_template_objective_id] FOREIGN KEY([proficiency_objective_id])
REFERENCES [NRS].[proficiency_objective] ([proficiency_objective_id])
GO
ALTER TABLE [NRS].[proficiency_test_template] CHECK CONSTRAINT [fk_proficiency_test_template_objective_id]
GO
ALTER TABLE [NRS].[proficiency_test_template_session]  WITH CHECK ADD  CONSTRAINT [fk_template_session_program_session_id] FOREIGN KEY([program_session_id])
REFERENCES [NRS].[program_session] ([program_session_id])
GO
ALTER TABLE [NRS].[proficiency_test_template_session] CHECK CONSTRAINT [fk_template_session_program_session_id]
GO
ALTER TABLE [NRS].[student_rank]  WITH CHECK ADD  CONSTRAINT [fk_student_rank_program_id] FOREIGN KEY([program_id])
REFERENCES [NRS].[program] ([program_id])
GO
ALTER TABLE [NRS].[student_rank] CHECK CONSTRAINT [fk_student_rank_program_id]
GO
ALTER TABLE [NRS].[student_rank]  WITH CHECK ADD  CONSTRAINT [fk_student_rank_program_session_id] FOREIGN KEY([program_session_id])
REFERENCES [NRS].[program_session] ([program_session_id])
GO
ALTER TABLE [NRS].[student_rank] CHECK CONSTRAINT [fk_student_rank_program_session_id]
GO
ALTER TABLE [NRS].[student_rank]  WITH CHECK ADD  CONSTRAINT [fk_student_rank_student_id] FOREIGN KEY([student_id])
REFERENCES [NRS].[student] ([student_id])
GO
ALTER TABLE [NRS].[student_rank] CHECK CONSTRAINT [fk_student_rank_student_id]
GO

 
\$\endgroup\$
3
  • \$\begingroup\$ want to redesign so it's more normalized -> Most relational DB designs strive for 3rd normal form. What form is the existing design & into what form do you want it? \$\endgroup\$
    – radarbob
    Commented May 18 at 3:09
  • \$\begingroup\$ Is this code finished? CodeReview is about complete & working code. Try StackOverflow or DatabaseAdministrator \$\endgroup\$
    – radarbob
    Commented May 18 at 3:24
  • \$\begingroup\$ The table design is confusing because no data elements/columns/fields are shown. For example "Student Rank" is a separate table but I'm thinking it should be a "Student" table field. And "Rank" has no association with "class" (class-day?) as stated in Business Rules. \$\endgroup\$
    – radarbob
    Commented May 18 at 3:31

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.