Design for reporting unique records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Design for reporting unique records

Author

Topic

dbwinger
Starting Member

5 Posts

Posted - 2007-04-12 : 08:22:13

Hello,

I'm hoping someone can point me in the right path with a design issue I'm running into. I'm somewhat new with database design and SQL server, so bear with me.

I'm creating a sort of project management system, and I've got a main PROJECT table, and then a STUDENT_PROJECT_PARTICIPANT table, with foreign keys to STUDENT and PROJECT tables. Each has an 'institute' field. What I want to do is to be able to report on the number of students involved in each project, the number of unique students involved in project with a particular institute, and then the number of unique students involved in all projects. I'd also like to be able to report the number of unique students involved in a custom-picked list of projects (this seems the least feasible). The trick is that I need to report this statics within a specific date range. My original idea was to just save statistics of the numbers of students involved in a project every time it is saved, but from there I'm not sure how I can find out the unique count for all projects, since I don't have the date that each student was saved. That's my basic problem. Let me know if you need any clarification.

If anyone has done something similar or has any advice for me on this, please let me know!

Thanks!
Daryl

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-04-12 : 10:19:59

Post the DDL of your tables so we can get a better handle on the design. I'm assuming that you have a start and end date in your STUDENT_PROJECT_PARTICIPANT table to allow you to do date range queries. Assuming this is the case, I can't see any major problems... Once you've posted the DDL, it'll be easier to give you some pointers on the queries.

Mark

dbwinger
Starting Member

5 Posts

Posted - 2007-04-12 : 10:31:47

Mark,

Thanks for your response. The truth is that the tables are a lot more complex than I described. I'll post the DDL, but it may be more than you care to see. As you can see, I have a PROJECT_HISTORY table, which would be populated with statistics (such as the number of student participants) and the datetime each time a project is saved. The problem is that I only have a number of students for a date range, and I don't have a listing of which actual students were added during that reporting period. My initial idea, was to also have an INSTITUTE_PROJECT_HISTORY table and a TOTAL_PROJECT_HISTORY table and save the stats in them each time a project is saved as well, but that approach can't help me with trying to get unique student totals for a custom list of projects.

Here is the DDL for the tables I think you'd like to see:

CREATE TABLE [dbo].[PROJECT](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[parent_project] [int] NULL,
[institute] [int] NULL,
[phase] [int] NULL,
[manager] [nvarchar](50) NULL,
[summary] [nvarchar](max) NULL,
[city] [nvarchar](50) NULL,
[state] [nvarchar](50) NULL,
[country] [nvarchar](50) NULL,
[location_detail] [nvarchar](50) NULL,
[event_date] [datetime] NULL,
[reach_text] [nvarchar](max) NULL,
[research_focus] [bit] NULL,
[research_focus_text] [nvarchar](max) NULL,
[new_methodology] [bit] NULL,
[new_methodology_text] [nvarchar](max) NULL,
[completed] [int] NULL,
[immersive] [bit] NULL,
[lock] [datetime] NULL,
CONSTRAINT [PK_Project_1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PROJECT] WITH CHECK ADD CONSTRAINT [FK_Project_Project] FOREIGN KEY([parent_project])
REFERENCES [dbo].[PROJECT] ([id])
GO
ALTER TABLE [dbo].[PROJECT] CHECK CONSTRAINT [FK_Project_Project]

CREATE TABLE [dbo].[STUDENT_PROJECT_PARTICIPANT](
[project_id] [int] NOT NULL,
[student_id] [int] NOT NULL,
[designer] [bit] NULL,
[researcher] [bit] NULL,
[research_part] [bit] NULL,
[immersive] [bit] NULL,
CONSTRAINT [PK_STUDENT_PROJECT_PARTICIPATION] PRIMARY KEY CLUSTERED
(
[project_id] ASC,
[student_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[STUDENT_PROJECT_PARTICIPANT] WITH CHECK ADD CONSTRAINT [FK_STUDENT_PROJECT_PARTICIPANT_Project] FOREIGN KEY([project_id])
REFERENCES [dbo].[PROJECT] ([id])
GO
ALTER TABLE [dbo].[STUDENT_PROJECT_PARTICIPANT] CHECK CONSTRAINT [FK_STUDENT_PROJECT_PARTICIPANT_Project]
GO
ALTER TABLE [dbo].[STUDENT_PROJECT_PARTICIPANT] WITH CHECK ADD CONSTRAINT [FK_STUDENT_PROJECT_PARTICIPANT_STUDENT] FOREIGN KEY([student_id])
REFERENCES [dbo].[STUDENT] ([id])
GO
ALTER TABLE [dbo].[STUDENT_PROJECT_PARTICIPANT] CHECK CONSTRAINT [FK_STUDENT_PROJECT_PARTICIPANT_STUDENT]

CREATE TABLE [dbo].[PROJECT_HISTORY](
[id] [int] IDENTITY(1,1) NOT NULL,
[project_id] [int] NOT NULL,
[institute_id] [int] NULL,
[date] [datetime] NOT NULL,
[indiana_reach] [bit] NULL,
[national_reach] [bit] NULL,
[international_reach] [bit] NULL,
[student_participation] [int] NULL,
[immersive_experience] [int] NULL,
[student_designers] [int] NULL,
[student_researchers] [int] NULL,
[student_research_participants] [int] NULL,
[k12_participants] [int] NULL,
[adult_participation] [int] NULL,
[faculty_participation] [int] NULL,
[campus_partners] [int] NULL,
[industry_partners] [int] NULL,
[media_products] [int] NULL,
[research_focus] [bit] NULL,
[remote_technology] [int] NULL,
[new_methodology] [bit] NULL,
[presentations] [int] NULL,
[academic_publications] [int] NULL,
[industry_publications] [int] NULL,
[media_coverage] [int] NULL,
[awards] [int] NULL,
[external_funding] [money] NULL,
[evaluations] [int] NULL,
[in_kind_service_hours] [decimal](18, 0) NULL,
CONSTRAINT [PK_PROJECT_HISTORY] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

And here is my stored procedure to save the project history:

ALTER PROCEDURE [dbo].[procSaveProjectHistory](@project_id int)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @institute_id int, @research_focus bit, @new_methodology bit

SELECT @institute_id = institute FROM PROJECT WHERE id = @project_id
SELECT @research_focus = research_focus FROM PROJECT WHERE id = @project_id
SELECT @new_methodology = new_methodology FROM PROJECT WHERE id = @project_id

INSERT INTO PROJECT_HISTORY
VALUES (@project_id,
@institute_id,
getdate(),
ISNULL(dbo.fnProjectIndianaReach(@project_id),0),
ISNULL(dbo.fnProjectNationalReach(@project_id),0),
ISNULL(dbo.fnProjectInternationalReach(@project_id),0),
dbo.fnProjectStudentParticipation(@project_id),
dbo.fnProjectImmersiveStudents(@project_id),
dbo.fnProjectStudentDesigners(@project_id),
dbo.fnProjectStudentResearchers(@project_id),
dbo.fnProjectStudentResearchParticipants(@project_id),
dbo.fnProjectK12Participants(@project_id),
dbo.fnProjectAdultParticipants(@project_id),
dbo.fnProjectFacultyParticipants(@project_id),
dbo.fnProjectCampusPartners(@project_id),
dbo.fnProjectIndustryPartners(@project_id),
dbo.fnProjectMediaProducts(@project_id),
ISNULL(@research_focus,0),
dbo.fnProjectRemoteTechnology(@project_id),
ISNULL(@new_methodology,0),
dbo.fnProjectPresentations(@project_id),
dbo.fnProjectAcademicPublications(@project_id),
dbo.fnProjectIndustryPublications(@project_id),
dbo.fnProjectMediaCoverage(@project_id),
dbo.fnProjectAwards(@project_id),
dbo.fnProjectExternalFunding(@project_id),
dbo.fnProjectEvaluations(@project_id),
dbo.fnProjectInKindHours(@project_id))
END

Let me know if you want to see any other tables or SPs.

Boddedashiva
Starting Member

6 Posts

Posted - 2007-04-16 : 07:35:34

Hi,
You add two more columns to the tables given above as EntryDate and Enteredby which will be easy for you to monitor and you can also pickup the data based on date.

Jai

Boddedashiva
Starting Member

6 Posts

Posted - 2007-04-16 : 07:35:35

Hi,
You add two more columns to the tables given above as EntryDate and Enteredby which will be easy for you to monitor and you can also pickup the data based on date.

Jai

dbwinger
Starting Member

5 Posts

Posted - 2007-04-16 : 13:42:22

Thanks for the reply, Jai. I think adding the EntryDate column to my STUDENT_PROJECT_PARTICIPANT table would solve this problem. Unfortunately, the customer just laid another requirement on me, which is thus far stumping me. When editing a project, he would like to see a listing of student participants, with indication of whether they were involved or not involved in each reporting period and would also like to be able to edit whether they were involved for the current reporting period.

My initial idea is to add a 'reporting_period' field to STUDENT_PROJECT_PARTICIPANT (maybe instead of the 'EntryDate') and any include reporting_period in the primary key. Only STUDENT_PROJECT_PARTICIPANT rows with the current reporting period would only be able to be added/deleted/updated (by the user). My question now is how to get data to display with the following columns:

STUDENT.first_name, STUDENT.last_name, [Student participated in reporting period 1], [Student participated in reporting period 2], ..., [Student participated in reporting period n]

Additionally, I need to not show multiple rows with the same student_id.
Please let me know if you need any more clarification and if you can help.

Thanks!
Daryl

Boddedashiva
Starting Member

6 Posts

Posted - 2007-04-17 : 06:35:20

Hi Dary,

I have couple of questions on this

1) How are you Tracing the user login from forntend

2) What do you meant by reporting period (Is it is the Period logged in by the user?)

Regards
Jai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources