Relationships Headache

[simon]

Can anyone help me please?

I am building a Database but am unsure about relationships here.....
my Database (This Database is for staff training).

I have a table for EVENTS. with a 1-to-many relationship with the
COURSE table. (i.e. i wish to be able to assign multiple COURSES to
each EVENT)

I also has a PERSON table. I wish to be able to add multiple PERSON
records to each COURSE but i am unsure as to where i have the
relationship because if i have a 1-to-many COURSE-to-PERSON it means
each PERSON record is permanantly linked to that course (presuming i
have a COURSEID long-integer field within PERSON table) and i need to
be able to allocate the same person to more than one COURSE record.

Can anyone help me please? i am getting really confused

Many many thanks

 

[Stefan Hoffmann]

hi Simon,

I also has a PERSON table. I wish to be able to add multiple PERSON
records to each COURSE but i am unsure as to where i have the
relationship because if i have a 1-to-many COURSE-to-PERSON it means
each PERSON record is permanantly linked to that course (presuming i
have a COURSEID long-integer field within PERSON table) and i need to
be able to allocate the same person to more than one COURSE record.

You normally have a many-to-many relation ship here. Therefore you must
create an additional table for mapping:

COURSE_PERSON: Course_ID, Person_ID

Course_ID and Person_ID have to be unique, so create a combined primary
key consiting of these two fields.

This gives you:

COURSE --1:n--> COURSE_PERSON <--n:1-- PERSON


mfG
--> stefan <--

 

[simon]

thanks stefan,

Not sure i understand here, i have 3 tables; EVENT, COURSE,PERSON.

now i create a new tabel COURSE/PERSON, but how/where do i link it?

Sorry if i am asking dumb questions here.

Simon

 

[simon]

i have created the table you suggested, and link COURSE to
COURSE_PERSON 1-to-many, and link PERSON to COURSE_PERSON 1-to-many
but it will only allow me to allocate ONE person to each course, or 1
course to each person!

i am really confused here, sorry

 

[Stefan Hoffmann]

hi Simon,

Not sure i understand here, i have 3 tables; EVENT, COURSE,PERSON.
now i create a new tabel COURSE/PERSON, but how/where do i link it?

Take a look at

http://www.databasedev.co.uk/many_to_many_example.html


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Simon,

i have created the table you suggested, and link COURSE to
COURSE_PERSON 1-to-many, and link PERSON to COURSE_PERSON 1-to-many
but it will only allow me to allocate ONE person to each course, or 1
course to each person!

Take a look at the relationships and the primary keys:

http://ste5an.org/many-to-many.zip


mfG
--> stefan <--