Database design help and tips for a small app used to coach tennis players.
-
Hi,I am a tennis coach and creating a database/app to automate tasks related to students, programs, practices, performances, and testing. I am finishing the database design and have a few questions, mostly regarding the program and practice "time" aspects of the system. First, here is a summary of the "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.
Reporting requirements:
For the advanced program (and maybe others later), a student's performance/Ranks are recorded in each 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.
Below I have a conceptual design and DDL code with sample data, but there are a few questions/concerns.
I know the student_rank table is not normalized, but I've made this work for this season.
I have a vb/excel app that connects to the db that helps record practice/rank and session testing data.
I want redesign and make the db normalized and correct, so just looking for a few tips or suggestions on the best db design.
My main questions are about the "time" entities (program, session, week, season, and practice days.
I think I need a few more entities:
Season, session, program_session, Week table?, Date table?
How is the best way to normalize the student_rank data?
Thank you!
JB
High level design:
Logical Db Design:
DDL that is currently designed:
use master
GO
drop database if exists JBtennis
GO
create database JBtennis
GO
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,
[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,
[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
- This topic was modified 5 months, 3 weeks ago by Jason Wilks.
- This topic was modified 5 months, 3 weeks ago by Jason Wilks.
- This topic was modified 5 months, 3 weeks ago by Jason Wilks.
- This topic was modified 5 months, 3 weeks ago by Jason Wilks.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
It's tl;dr with the data. The real questions are: 1) what is your data access pattern, and 2) how are you handling identity and access management?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
The ERD is nice, thanks for that.
But I think you need to step back and do the logical design first (designing using Entities and Attributes rather than tables and columns), and following the standard normalization process. Skipping ahead to physical tables is a very bad idea.
You can then also identity actual data keys rather than automatically assigning an "id" as a key for every table, which is terrible practice, Instead, consider the keys for each table based on that specific table's design and requirements. When going to the physical design, pay special attention to getting the best clustering key on every table.
Edit: I tried to respond to this q earlier but the page kept timing out before I could get in. I think you should move the data to an attachment, even though people here aren't fond of attachments.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
I forgot to include the logical design ! I added it, but is using surrogate keys really that bad? I do have a few alternate keys defined.
To show the access pattern, here is the main query.. There are several others that are similar, but the calculations, parameters, and predicate are very similar.
SELECT s.full_name
,DATEDIFF(YEAR, s.dob, GETDATE()) AS age
,(ps.session_desc + ' - ' + ps.season) AS TestDate
,pc.shot_category AS Section
,po.shot_objective AS Round
,pt.score
,po.score_attempts
,ROUND(CAST((pt.score * 100.0 / po.score_attempts) AS FLOAT), 1) AS Percentage
,pc.proficiency_category_id
,po.proficiency_objective_id
FROM NRS.proficiency_test pt
JOIN nrs.proficiency_test_template ptt ON pt.proficiency_objective_id = ptt.proficiency_objective_id
JOIN nrs.proficiency_test_template_session ptts ON ptt.template_num = ptts.template_num
AND pt.program_session_id = ptts.program_session_id
JOIN nrs.proficiency_objective po ON ptt.proficiency_objective_id = po.proficiency_objective_id
JOIN nrs.proficiency_category pc ON po.proficiency_category_id = pc.proficiency_category_id
JOIN nrs.student s ON pt.student_id = s.student_id
JOIN nrs.program_session ps ON pt.program_id = ps.program_id
AND ptts.program_session_id = ps.program_session_id
WHERE (ps.session_desc + ' - ' + ps.season) IN @pSession
AND s.full_name IN @pStudentName
ORDER BY s.full_name
,(ps.session_desc + ' - ' + ps.season)
,pc.proficiency_category_id
,po.proficiency_objective_id - Jason Wilks wrote:
... but is using surrogate keys really that bad? ...
Surrogate keys are not necessarily bad, if chosen after proper analysis, but just automatically defaulting to using a surrogate key for every table, yes, that is really that bad.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
What I am struggling with the most is whether to add a "Calendar" table, what to populate with and how to maintain it. I think I need one because I have duplicated data in the student_rank table (week_id, day_desc).
So I can just store a date] column in student_rank. Then link to Calendar(date, week_id, day_desc, session_id, week_off_yn, [all other date variation columns]). I'll just have to maintain and populate this calendar table at the start of each season.
After I initially create the calendar table, I will need to loop through it populate the week_id associated to each date (there are 8 weeks in each "session"), and some weeks are "Off" so they are not consecutive calendar weeks. For example for this season, in Session2, there are 2 separate weeks off; one for thanksgiving and two consecutive weeks over Christmas. So I would start this update script declaring these variables:
DECLARE @FY23_Session1_StartDate date = '2023-09-04'
DECLARE @FY23_Session1_EndDate date = '2023-10-29'
DECLARE @FY23_Session2_StartDate date = '2023-11-06'
DECLARE @FY23_Session2_EndDate date = '2024-01-21'
DECLARE @FY23_WeekOff1_Session2_StartDate date = '2023-11-20'
DECLARE @FY23_WeekOff1_Session2_EndDate date = '2023-11-26'
DECLARE @FY23_WeekOff2_Session2_StartDate date = '2023-12-25'
DECLARE @FY23_WeekOff2_Session2_EndDate date = '2024-01-07'
Then loop through the Calendar table and update the weeknum field (1-8,) and skipping the weekoff weeks (maybe make the weeknum field 0 ?). This is getting a little more complicated than I though so seems like this is not the right or best way to design it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply