Design help needed

Question

Post reply

Design help needed

descentflower

Ten Centuries

Points: 1227
More actions
June 30, 2009 at 9:20 am

#133495

Hi,
I would be thankfull if any of you can help me out.
Here is the problem:
I have a Table tblNames which has unique entries of students.
Each student can have atleast 2 optional subjects selected. So 'John' can select Maths,Science where as 'Sam' can have History,Geography,Science etc.
Pool of optional subjects from where students select can increase.
How should i save these chhosen subjects in my table tblNames?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Greg J SSCarpal Tunnel Points: 4288 More actions · Answer 1

1 approach would be to have a many:many facilitator table with 2 columns:

student | subject

Ned | Maths

Ned | Science

Ned | History

Paul | Maths

Paul | Drama

I'd use an INT [id] column from each underlying table (student/subject) instead of the name strings I used above.

Having subj1, subj2, subj3, subj4, ... subjN columns inside the tbStudent table is going to give you headaches.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 2

I agree. I wouldn't add that data to your Names table. You should go for a more normalized approach with a Subjects table and, like the last post said, a Names to Subjects interim table. You could add a flag to that table that marks a given subject as optional, but then you'd also want to create a constraint to make sure that you only ever have two marked as optional.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

descentflower Ten Centuries Points: 1227 More actions · Answer 3

Hi,

So i will have 3tables. viz.

1) tbl_Names : All students names (with a unique id nId).

2) tbl_AllSubjects : A pool to have all the subjects from where students can choose (with unique id nSId).

3) tbl_ChoosenSubjects : Here each nId will have a nSId. (with a unique id nCId)

Have i understood u all correctly?

Please correct me if i m wrong.

Thanks

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 4

That looks like you're on the right track to me.

By the way, it's kind of a waste of space & time & readability to put 'tbl_' in front of table names. No big deal, but it's not really considered a good practice.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

descentflower Ten Centuries Points: 1227 More actions · Answer 5

Hi,

I used the normalized design for my case.

If i want my output to be in this form for a student then?

Name SelectedSubjects

--------------------------

A Maths,History,Drama

B Science,Econ

i.e. A comma sepearted Selected Subjects for a respective student.

Thanks.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 6

That's a pivot operation. Do a search on it. I'm not great at them, so you'd be better off looking up what the real experts have to say.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

descentflower Ten Centuries Points: 1227 More actions · Answer 7

Hi,

I did this,

[Code]

SELECT STUFF((SELECT ', ' + Table.ColumnName FROM Table FOR XML PATH('')),1, 1, '') AS CSVColumn

[/Code]

DCPeterson SSCoach Points: 15266 More actions · Answer 8

This is a super simplified version, but I think you should get the idea...

create table student (firstname varchar(25) primary key)

go

create table class (className varchar(25) primary key)

go

create table student_class (StudentName varchar(25), ClassName varchar(25))

insert student

select 'a'

union all

select 'b'

union all

select 'c'

insert Class

select 'Math'

union all

select 'Biology'

union all

select 'PE'

union all

select 'Literature'

insert student_class

select 'a', 'Literature'

union all

select 'a', 'Math'

union all

select 'a', 'PE'

union all

select 'b', 'Biology'

union all

select 'b', 'PE'

union all

select 'c', 'Math'

select studentName,

Classes = REPLACE(

(

select [data()] = className

from dbo.student_class sc

where sc.StudentName = sc1.StudentName

for xml path('')

), ' ', ', ')

from dbo.student_class sc1

group by StudentName

order by StudentName

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 9

First you should look at: http://www.datamodel.org/NormalizationRules.html

That will give you the basics. I'm not sure if this is for a class, personal enrichment or work. But, if you plan on continuing to work with databases I'd sugget reading and understanding the "Bible" for database professionals: http://www.amazon.com/Introduction-Database-Systems-C-J-Date/dp/0321197844

To your specific problem. You'll want to do something similar to the suggestion by Descentflow, but I would suggest you leave off the nCID on the ChoosenSubjects table. Other than that it looks pretty solid.