DW Table Design with Indexes - SQL Server Forums

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

DW Table Design with Indexes

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-01-27 : 12:55:02

I would like a second set of eyes or more on this piece of a Data Warehouse I am designing.

The system I get the data from ONLY keeps the data for the current school year(@ the begining of each school year the tables are cleared). I am trying to capture what occurs during the course of a year & for each year to come.

A student can be in multiple Programs(such as free lunch, learning disabilty, gifted, mobile(moves multiple times within or in/out of disctrict in same school year), etc.) AND they can move in/out of these Programs more than once each school year. Currently when a student moves in or out of a program a 'STATUS' column is modified with a A(active) or I(inactive). Each Program is in a different table or tables and I want to combine them into ONE table called PROGRAMS.

Here is what I have come up with and would like to know if this is the 'best practice' way or if I am looking at this from the wrong angle.

SID = Student ID(which the other system assigns, Unique).

CREATE TABLE STUDENT
(
	SID			INT			NOT NULL, 
	LName			VARCHAR(30)		NOT NULL,
	FName			VARCHAR(30)		NOT NULL,
	MIDName			VARCHAR(15)		NOT NULL,
	
	CONSTRAINT PK_STUDENT PRIMARY KEY CLUSTERED (SID ASC)
)
GO

CREATE TABLE PROGRAMS
(
	SID			INT	NOT NULL,
	SchoolYR		CHAR(9)	NOT NULL, 
	ProgType		CHAR(6)	NOT NULL,
	RecordDT		DATE	NOT NULL,
	
	CONSTRAINT PK_PROGRAMS PRIMARY KEY CLUSTERED (SID ASC, SchoolYR DESC, ProgType ASC, RecordDT DESC),
	CONSTRAINT FK_PROGRAMS_STUDENT FOREIGN KEY (SID) REFERENCES STUDENT (SID)
)

RecordDT = the date the Program was inserted/updated in the other system.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 13:05:06

for easy manitainability i would add an identity column in second table and make it pk rather than composite combination.I cant see where you're including status columns though

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-01-27 : 13:47:07

Let me point out I am NEW to SQL Server (and this is my FIRST at Designing of a Database ever) but have been a SQL developer on other platforms.

The Status would be in the Programs table. Sorry about leaving that out.

I was just trying to come up with a way to 'easily' determine how many Students are in a Program at any given time in my design. Maybe SchoolYR should be above SID?

In the future reports would want to show if a Student did good/bad per Program in a given SchoolYR. Plus if moving in/out multiple times shows any 'trends' to student learning.

I don't want to start anything but I seen where some don't like Identity and I currently do not have any in this Database.

I thank you for your time & suggestion & I will create another set of tables using identity & see if it works better than what I have so far.

Thanks and keep posting suggestions.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-01 : 08:41:27

Hm, seems like no one was interested anymore.

If I understand right the programs-table will have information about which students are a part of one or more programs in a school year, right? If that's the case then I think it would be better to replace the RecordDT column with a MemberFromDT and a MemberToDT instead and if the MemberToDT is empty then it means the the student is currently a member of the program, hence a status column will not be necessary (unless there are other statuses as well). Here is an example of how a problem like this is usually solved (students table is ok):

CREATE TABLE PROGRAMS (
	ProgID int,
	ProgName varchar(100),
	...
)

CREATE TABLE StudentPrograms
(
	SID	INT	NOT NULL,
	ProgID int not null,
	MemberFromDT datetime not null default(getdate()),
	MemberToDT datetime null
)

- Lumbago
My blog-> www.thefirstsql.com

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-02-01 : 12:22:22

Yes, I was 2nd & 3rd guessing about using a 'begin' & 'end' DT but didn't think of seperating things into 2 tables as you did. I see how this is much easier to work with.

Thanks for spending the time to think of & show me another way.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-02-06 : 23:31:59

how will you know wether a student is in the program or not...i mean to say 'STATUS'

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2011-02-07 : 13:18:22

quote:
Originally posted by ahmeds08

how will you know wether a student is in the program or not...i mean to say 'STATUS'

Where EndDT is NULL would indicate that a student is in a program. Once they leave the program EndDT would then have that days(date) in it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources