Create a SQL table with a varying number of columns

Question

I have two tables, say teacher and student. I want to build a third table called class.

The class table will have one column for the teacher of the class, but I want to see if their is an elegant way to represent the students. My first thought is to have say 30 columns.. student1, student2 and have quids for each of these that tie back to a row in the student table.

But I am asking to see if there is a more preferred solution. The above solution seems clunky.

Are you asking about how to store the students that are in each class, or how to select them such that you will see a column for each student in the class? The structure of your tables does not necessarily have to correspond to how you would like your query results to look. — Jeff Ogata
– Jeff Ogata, Commented Sep 21, 2010 at 18:06
if you ever have a SQL table design where you have columns of the same name except an attached incrementing number (like your student1, student2...), you have a bad design! this is why they invented the phrase "just say no" — KM.
– KM., Commented Sep 21, 2010 at 18:43
+1 for detecting the "database smell" of multiple columns when they should be rows. — ErikE
– ErikE, Commented Sep 21, 2010 at 19:15
@KM, yeah I knew it was bad was trying to think of a couple different ways to do it right. — Maestro1024
– Maestro1024, Commented Sep 23, 2010 at 18:34

Jane T · Accepted Answer · 2010-09-21 18:01:13Z

4

If it was me I would have a 4th table called attendees or similar which links the students to the class as it's a many to many relationship. Which would contain the Class ID and the Student ID as a minimum..

answered Sep 21, 2010 at 18:01

Jane T

2,1212 gold badges17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MikeD Over a year ago

+1 ... by all means yes ... I would do the same for teachers because is there only 1 and only 1 teacher for each class? e.g. ClassDetail(ClassID, PersonID, Role)

Maestro1024 Over a year ago

Selected this answer as it is the most straight forward. I need to look at the PIVOT solution but this solution works for now.

Denis Valeev Over a year ago

@Maestro1024 Take a look at my StudentClass table, it's exactly that.

Denis Valeev · Accepted Answer · 2010-09-21 17:58:28Z

3

The class "table" is not a table but a result of running a stored procedure with pivoting of data.

And data structure is as follows:

Student: Id, ...
Teacher: Id, ...
StudentClass: StudentId, ClassId, ...
Class: Id, TeacherId, ...

answered Sep 21, 2010 at 17:58

Denis Valeev

6,03537 silver badges41 bronze badges

Comments

SQLMenace · Accepted Answer · 2010-09-21 17:59:46Z

3

SQL Server has this thing called sparse columns However I would advice against it. Normalize your data and then PIVOT/crosstab the results when you want to display it side by side

answered Sep 21, 2010 at 17:59

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Comments

user114600 · Accepted Answer · 2010-09-21 17:58:44Z

2

It's very clunky.

~~Have three tables:~~

Teacher

Student

~~TeacherStudent, which is a many-many table to join the two.~~

Do the pivot method expressed in the other answer. It makes more sense.

answered Sep 21, 2010 at 17:58

user114600

1 Comment

user114600 Over a year ago

Thanks for the upvotes, but I think he's after the PIVOT keyword.

Collectives™ on Stack Overflow

Create a SQL table with a varying number of columns

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related