SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Design for reporting unique records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbwinger
Starting Member

USA
5 Posts

Posted - 04/12/2007 :  08:22:13  Show Profile  Reply with Quote
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

United Kingdom
735 Posts

Posted - 04/12/2007 :  10:19:59  Show Profile  Reply with Quote
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
Go to Top of Page

dbwinger
Starting Member

USA
5 Posts

Posted - 04/12/2007 :  10:31:47  Show Profile  Reply with Quote
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.
Go to Top of Page

Boddedashiva
Starting Member

India
6 Posts

Posted - 04/16/2007 :  07:35:34  Show Profile  Click to see Boddedashiva's MSN Messenger address  Reply with Quote
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
Go to Top of Page

Boddedashiva
Starting Member

India
6 Posts

Posted - 04/16/2007 :  07:35:35  Show Profile  Click to see Boddedashiva's MSN Messenger address  Reply with Quote
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
Go to Top of Page

dbwinger
Starting Member

USA
5 Posts

Posted - 04/16/2007 :  13:42:22  Show Profile  Reply with Quote
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
Go to Top of Page

Boddedashiva
Starting Member

India
6 Posts

Posted - 04/17/2007 :  06:35:20  Show Profile  Click to see Boddedashiva's MSN Messenger address  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.26 seconds. Powered By: Snitz Forums 2000