Two foreign keys pointing to the same primary key in other table

[ravichoudhari]

i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -primary key, FirstName, LastName, ........

subjects
ID->primary key, subject

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

now if in the student_subjects table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,

how can i join table in SQL query to access the student_subjects table
so that i can display the result as

ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?

for example :

ID sub1 sub2
sub3 ........................ sub6
1000 telecom. cryptography
networking ....................... cgi/perl

[Allen Browne]

The 3rd table is not right.

Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7

(It is actually possible to create multiple relationships from one table to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ra***********@gmail.comwrote in message
news:11**********************@y66g2000hsf.googlegr oups.com...

>i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -primary key, FirstName, LastName, ........

subjects
ID->primary key, subject

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

now if in the student_subjects table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,

how can i join table in SQL query to access the student_subjects table
so that i can display the result as

ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?

for example :

ID sub1 sub2
sub3 ........................ sub6
1000 telecom. cryptography
networking ....................... cgi/perl

[Tom van Stiphout]

On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.

The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.

-Tom.

>The 3rd table is not right.

Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7

(It is actually possible to create multiple relationships from one table to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)

[Allen Browne]

Perhaps I misread the original question, Tom.

The way I read it, there might be occasions when the StudentID + SubjectID
is not unique. For example, the student might submit 6 preferences this
year, do 4 subjects, and then submit one of the other two as a preference
again next year.

If you don't need that flexibility, ravichoudhari, then Tom's suggestion is
better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom van Stiphout" <no*************@cox.netwrote in message
news:cj********************************@4ax.com...

On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.

The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.

-Tom.

>>The 3rd table is not right.

Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7

(It is actually possible to create multiple relationships from one table
to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)

[ravichoudhari]

On Mar 22, 3:07 pm, "Allen Browne" <[e-mail]:

Perhaps I misread the original question, Tom.

The way I read it, there might be occasions when the StudentID + SubjectID
is not unique. For example, the student might submit 6 preferences this
year, do 4 subjects, and then submit one of the other two as a preference
again next year.

If you don't need that flexibility,ravichoudhari, then Tom's suggestion is
better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom van Stiphout" <[e-mail] in messagenews:cj********************************@4ax .com...

On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<[e-mail]:

Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.

The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.

-Tom.

>The 3rd table is not right.

>Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7

>(It is actually possible to create multiple relationships from one table
to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)

the solution of having studentid & subjectid as you have specified can
hold multiple records of a student and gives lot of flexibility (in
fact a good design of table), but what i need in my application
displaying the records of each student in a flex grid along with the
the subjects (subject names) as a single record in a row

ex:

st1 sub1 sub2 ........
st2 sub1 sub2 ........

where sub1 ..... should be actual names of subjects
(subjetcs.subject).

[Allen Browne]

So you want to concatenate the subject names back together as one long
string again for display purposes?

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ra***********@gmail.comwrote in message
news:11**********************@y80g2000hsf.googlegr oups.com...

On Mar 22, 3:07 pm, "Allen Browne" <[e-mail]:

>Perhaps I misread the original question, Tom.

The way I read it, there might be occasions when the StudentID +
SubjectID
is not unique. For example, the student might submit 6 preferences this
year, do 4 subjects, and then submit one of the other two as a preference
again next year.

If you don't need that flexibility,ravichoudhari, then Tom's suggestion
is
better.

"Tom van Stiphout" <[e-mail] in
messagenews:cj********************************@4a x.com...

On Thu, 22 Mar 2007 10:02:57 +0900, "Allen Browne"
<[e-mail]:

Two fields is enough:
StudentID
SubjectID
and don't forget to create a primary key over the combination of those
two fields.

The extra ID column can come in handy if you're linking this table to
other tables, but if it's only used as a "junction table", there is no
need.

-Tom.

>>The 3rd table is not right.

>>Table 3 needs fields:
ID primary key
StudentID foreign key
SubjectID foreign key
Now you have need only one relationship to the Subject table.
The idea is to have many records here, instead of repeating fields. So
if
Student 24 enrols in subjects 2,4, and 7, you have records like this:
StudentID SubjectID
24 2
24 4
24 7

>>(It is actually possible to create multiple relationships from one
table
to
another by adding multiple copies of the table into the Relationships
window, but that is to solve a different problem.)

the solution of having studentid & subjectid as you have specified can
hold multiple records of a student and gives lot of flexibility (in
fact a good design of table), but what i need in my application
displaying the records of each student in a flex grid along with the
the subjects (subject names) as a single record in a row

ex:

st1 sub1 sub2 ........
st2 sub1 sub2 ........

where sub1 ..... should be actual names of subjects
(subjetcs.subject).

[Last Boy Scout]

That is an invalid table structure. It is not in normal form and it is
prone to error.

Since there are many subject for the student and many student for the subjet
you need a join table with student id and subject id. To make it function
properly. Basically you have a table inside a table and that does not work.
<ra***********@gmail.comwrote in message
news:11**********************@y66g2000hsf.googlegr oups.com...

>i came accross requirement of multiple foreign keys in a table
referencing the same primary key another table. i created the table
relations using the relations editor in access. even though i could
have multiple foreign keys to a table access did allow the referential
integrity.

my database structure is

table1 - students table
table 2 - subjects table
table 3 - subjects opted by students

in the table 3 totally there are 6 subject columns and a student can
opt for variable number of subjects.

table structure

students
ID -primary key, FirstName, LastName, ........

subjects
ID->primary key, subject

student_subjects
ID->primary key, StudentID->foreign key referencing students.ID, and
sub1, sub2, .....sub6 all of which reference the primary key in
subjects table.

now if in the student_subjects table i have a record with data
ID-1000, StudentID-100, sub1-1, sub2-2......sub6-6,

how can i join table in SQL query to access the student_subjects table
so that i can display the result as

ID->studentID (name not required)
but from sub1 to sub6 i should display the actual subject name that is
in the subjects table ?

for example :

ID sub1 sub2
sub3 ........................ sub6
1000 telecom. cryptography
networking ....................... cgi/perl