June 16, 2016 at 8:08 am
Hi,
i need a bit of help redesigning a part of a model.
create table A
(
id_A int not null identity(1,1),
constraint PK_A primary key clustered (id_A)
)
go
create table B
(
id_B int not null identity(1,1),
constraint PK_B primary key clustered (id_B)
)
go
create table C
(
id_C int not null identity(1,1),
constraint PK_C primary key clustered (id_C)
)
go
create table AB
(
id_A int not null,
id_B int not null,
constraint PK_AB primary key clustered (id_A, id_B),
constraint FK_A_AB FOREIGN KEY (id_A) references A(id_A),
constraint FK_B_AB FOREIGN KEY (id_B) references B(id_B)
)
go
create table ABC
(
id_A int not null,
id_B int not null,
id_C int not null,
prop1 nvarchar(10) not null,
constraint PK_ABC primary key clustered (id_A, id_B, id_C),
constraint FK_AB_ABC FOREIGN KEY (id_A, id_B) references AB(id_A, id_B),
constraint FK_C_ABC FOREIGN KEY (id_C) references C(id_C)
)
this is a simple modification of an existing model, back then, i never had the need to have the same object A combined with different B's
ex:
"Bio", "Eng. 1"
"Bio", "Eng. 2"
this is not a problem per se, i am stating that Bio is lecture in Engineering 1 and Engineering 2.
but when i want to define the properties of Bio in ex, 2016, this is where the problem begins
i don't want to keep the properties of Bio in 2016 duplicated, because they are the same "thing".
"Bio", "Eng. 1", 2016, "Intro"
"Bio", "Eng. 2", 2016, "Intro"
what is the best path to avoid storing this duplication, while keeping the state that Bio was lecture in 2016 on 2 different courses ?
Thank you,
Noah.
June 16, 2016 at 8:17 am
Hello,
No where in your tables design there is scope for accommodating the string data 'bio', 'eng.1' etc.
Can you eloborate the problem with data in tables.
June 16, 2016 at 8:25 am
the model is just a simplicafication, but
on table A you can have things like:
1, 'Bio'
2, 'Math'
on table B you can have things like:
1, 'Eng.1'
2, 'Eng.2'
on table AB you can have things like:
1, 1, 50 (credits)
1, 2, 45 (credits)
meaning Bio is lectured in Eng.1 (with 50 credits) and Eng.2 (with 45 credits)
hope it helps )
June 16, 2016 at 1:36 pm
There's extremely little detail here, but it seems as if you are not distinguishing between a course and a single offering of the course, which I'll call a "class" for the purposes of this discussion.
A course is a subject that the college can offer, such as "Eng 1".
A class is a specific offering of that subject, which would include a term (Spring 2016), an instructor(s), etc..
The course code contains the description and an offering date range, since the description and details of a course can vary over time. You don't duplicate the course data, such as the course name "Introduction to <whatever>" in the class offering, instead the course code and term allow you to lookup the course details.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 17, 2016 at 1:37 am
it's a relationship between units (classes) and a plan study.
a plan study describes the course path through the 1st, 2nd, 3rd year, ...
and, you're right at the end, i don't distinguish the course, because the offering is the same for the all the courses where she's offered.
when i add the year of lecture (id_C) is where i describe the properties of the unit, teaching date range, teachers, bibliography, .. all this is the same for every course at a given year.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply