Re: FN-FORUM: Database structure question

date posted 30th April 2007 14:20


> I have a database structure as follows:
>
> tblGroups
> ID
> Description
> _________
>
> tblCourseCategories
> ID
> GroupID
> Description
> _________
>
> tblCourses
> ID
> CourseCategoryID
> Description
> _________
>
>
> This works fine except my client has mentioned he would like to have
> the ability to add a course to more than 1 Course Category. Currently
> to do this, he has to add the course details twice so that it appears
> under 2 category listings.
>
> How would I go about changing the system so that he is able to just
> add a course once, and then link it to as many categories as he wants?
> Would I need an additional table that is used just for linking?
>
> I.E:
>
> tblCourseListings
> ID
> CourseID
> CourseCategoryID
>
> And then remove the CourseCategoryID field that acts as a foreign key
> from tblCourses.
>

Yes, that sounds exactly right. A course can have several categories and
a category can have several courses. This is the classic example of
where you'd want a link table. The CourseID and CourseCategoryID would
be foreign keys pointing to the course and category tables.

> Is this the right way to do things?
>
> TIA
> Paul
>