1

I'm using Microsoft SQL Server 2014.

Let's say I have a university database with 3 tables. One listing the courses, one the years, and one the coursework that's available to do.

CREATE TABLE Courses (
    Course_ID varchar(5) PRIMARY KEY NOT NULL,
    Name varchar(255) NOT NULL
)

CREATE TABLE Years (
    Year_ID tinyint
)

CREATE TABLE Coursework (
    CW_ID varchar(5) PRIMARY KEY NOT NULL,
    Name varchar(255) NOT NULL,
    Allowed varchar(255) NOT NULL
)

Now, a coursework might be allowed to any combination of courses/years. So, I could have a coursework that allows specifically students in year 1 of course X, another that allows students in courses X, Y and Z of years 2 and 3, one that allows students in course X of all years, one that allows students in years 1 and 2 regardless of course, or even one that allows all courses and all years. (I hope this gets my point through.)

"Allowed" would verify that the combination is valid, maybe by referencing the foreign keys of each table (courses and years).

But how would I do that? I've thought of creating a new table using a composite primary key, but that would require manual input and it would be limited to combinations in pairs.

So my guess is I would need to generate the possible combinations somehow, list them in a table with a unique primary key for each one, and reference that as a foreign key in the "Coursework" table. However, I'm a bit stuck. Any tips?

EDIT:

To clarify what I want:

Imagine the following Courses table:

Course_ID    Name

A            Arithmetics
B            Biology
C            Chemistry

And the following years table:

Year_ID

1
2
3

And now, a teacher creates a new coursework. They want to make it available to students in years 2 or 3, studying Biology or Chemistry. So it would be available to someone studying Biology on year 3, someone Studying Chemistry on year 2, but not for someone studying Biology on year 1 or someone studying Arithmetics on year 2.

They would insert the Coursework into the table.

CW_ID    Name             Allowed

SA       Soil analysis    B,C,2,3

This would be fine, but how would I check that they're entering values that exist, to stop them from entering "X,5" for example, as such course/year doesn't exist?

EDIT2:

I was testing something, but besides it definitely not being the best solution, I'm not even sure of how to implement it.

CREATE TABLE YesOrNo (
    YesOrNo_ID bit PRIMARY KEY NOT NULL
)

CREATE TABLE Allowed (
    Allowed_ID int IDENTITY(1,1),
    Arithmetics bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL,
    Biology bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL,
    Chemistry bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL,
    First bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL,
    Second bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL,
    Third bit FOREIGN KEY REFERENCES YesOrNo(YesOrNo_ID) NOT NULL
)

By populating the Allowed table with all the combinations of 1s and 0s (true or false) in all the courses/years, the teacher would only have to insert the Allowed_ID in the Coursework table and then it would be known which courses/years the coursework was allowed to.

However, it is impractical to manually add all the courses and years as attributes, and even then, I don't know how to populate the table automatically with all the possibilities.

Taking from the example, one of the table's rows would look like this:

Allowed_ID    Arithmetics    Biology    Chemistry    First    Second    Third

42            0              1          1            0        1         1

And the teacher would just input "42" into the allowed field in the Coursework table.

Improve this question
5
  • I'm struggling to understand what you are trying to achieve here. If all you want is a list of all possible combinations based on key values from different tables then you can do this with CROSS JOIN Commented Apr 8, 2016 at 20:05
  • Why do you want to materialize this? With even a small number of rows in each table that number of combinations gets staggering. What are you really trying to do here and we can help you find a better approach. Commented Apr 8, 2016 at 20:14
  • why u no google before ask? stackoverflow.com/questions/18716767/… Commented Apr 8, 2016 at 20:31
  • I just need that attribute to identify which students that coursework is available to (what courses, what years). I realise now that generating all possible combinations might not be the best solution, but I can't seem to identify another one... Commented Apr 8, 2016 at 20:41
  • Plus, I just did a cross join, but it's not really what I need after all, because it just lists all the courses with all the years. What I want is a way to select or store in the table what course(s) and year(s) is the coursework available to. Something like: (X,Y,Z,1,2,3) or (X,1,2,3) or (X,Y,1) Commented Apr 8, 2016 at 21:00

2 Answers 2

Reset to default
2

As a one-shot deal, you could do:

insert into Coursework (
    Course_ID ,
    Year_ID )
select
    Courses.Course_ID ,
    Years.YearID
from
    Courses
cross join
    Years

And if you wanted to get fancy & not add duplicates:

insert into Coursework (
    Course_ID ,
    Year_ID )
select
    Courses.Course_ID ,
    Years.Year_ID
from
    Courses
cross join
    Years
left join
    Coursework
on
    Courses.Course_ID = Coursework.Course_ID
and
    Years.Year_ID = Coursework.Year_ID
where
    Courses.CW_ID is null
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It says "Invalid column name 'Course_ID'." when I paste that into SQLFiddle with my code in. Any idea why?
Never mind, I had to add Course_ID and Year_ID as attributes to the Coursework table... But still, it doesn't quite solve the problem. Firstly, the Coursework would already be populated with CW_ID and Names, and this would disregard that. But even if I put all of this into a separate table (such as ValidCourseworkByYear), how do I state that a Coursework is available for all courses and all years, for example? Or for years 1 and 2 of course X, or anything else. As it is, it just states the combinations of courses/years (X,1) (X,2) (X,3) (Y,1) (Y,2) (Y,3) etc, but not things like (X,Y,1,2)
0

You want a many to many join table, not a single column, to record which pieces of work are valid for which courses and years.

CREATE TABLE ValidCourseworkByYear (
    VCWBY_ID int PRIMARY KEY IDENTITY(1,1),
    CW_ID varchar(5) NOT NULL,
    Course_ID varchar(5) NOT NULL,
    Year_ID tinyint NOT NULL
)

This creates links between pieces of work, courses and years without duplicating data or preventing it from being used more than once. If the entry doesn't exist in that table, then that particular work-course-year combination is invalid.

If your coursework table is already foreign keyed to courses (meaning that a piece of work is only ever usable in one course), then you can eliminate CW_ID from this table.

Also, don't use strings as primary keys; they perform poorly. If you want a short identifier for display purposes, make it a separate column and put a unique constraint on it.

Improve this answer

2 Comments

How would I add the actual information into that table? That just sets up the relations right?
Yes, the schema definition just defines the relation. The precise mechanics of populating the join table depends on whether you're inserting new data into the other tables, or documenting this relationship for existing data. Regardless, the year, course, and work data need to be put in their respective tables first, so you have the primary keys to insert into the join table. You could populate those tables, write a select query to get the keys for the appropriate relations out, then insert the result into the join table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.