Page 1 of 1

Normalising my database using phpMyAdmin

Posted: Fri Oct 21, 2005 6:32 am
by sleazyfrank
Hi there - I have a unnormalised mysql database that drives our course booking website. Time was short and building it in this poor way was the quickest to get the site off the ground (I had to learn php and mysql from scratch very quickly so there was no time to mess about trying to figure out how to build a properly constructed db :oops: ). Now with the site live and functioning (albeit a little slow at times) I have a little more time on my hands to reshape the db.

Basically there are 10 unlinked tables, each named after a particular topic area (Networking, Microsoft, Unix etc) that contain the same field information (RecordID, CourseTitle, Duration, Price, months from jan to dec, Location and Keywords).

I have written up a plan to normalise this into nine new tables:

***********
tblCourses
***********
courseID
scheduleID
topicID
courseTitle
Price
Duration
locationID
Keywords
********************
tblCourse_to_topics_link
********************
topicID
courseID
**********
tblTopics
**********
topicID
Topic
***********
tblLocation
***********
locationID
Location
**********************
tblCourse_to_schedule_link
**********************
courseID
scheduleID
*************
tblSchedule
*************
scheduleID
dayID
monthID
yearID
***********
tblDay
***********
dayID
Day
***********
tblMonth
***********
monthID
Month
***********
tblYear
***********
yearID
Year



The idea being that a course has a title, duration, price and a set of keywords unique to its record. Then the other ID fields are linked to the other tables, so that multiple courses can share the same schedule data etc. I don't know if I went overboard with the three date tables, but there you go. A course now can belong under multiple topics and not have the data repeated.

My php code will also be able to work out the end date of the course by using the schedule data and the duration field. That's all well and fine. But my problem is that I have no idea how to set up a relational db in mysql! lol In Access I got lazy by using the relational diagram software but I am using phpMyAdmin now which is still great but I can't figure out how to make my tables and fields relational.

So can anyone let me know how to do this please? many thanks

cheers.

frank