MySQL ?Relational? databases query

[Chris_J_W]

Hi, I'm trying to set up a bit of a database here with key relationships between the tables. (Forgive me if I don't use the right terminology in the right context - it's been a while since I've done databases).

Basically I've got a table containing people, with a primary key field "id". I also have subsequent tables for various courses these people can enrol in, so I've set up these subsequent tables to have a table names as the names of the courses, then the fields of the table to be this "id" field, and the names of various sub-courses within the main course.

Now what I want to do, is set it up so that if (for whatever reason), a record is deleted from the initial people table, then any records in the course tables with the same ID are deleted from those tables too. Am I making sense? How would I got about doing this? I've looked on the MySQL site, but without knowing the right terms for what I want, it's not easy to find the info :(

Any help would be greatly appreciated.

[Nedals]

I would switch thing around bit.

Any person could be enrolled into a number of different courses that overlap courses taken by another person. It's likely people will change but courses will remain relatively static.

Set your database up like this, then you can add or delete people at will.

people: pid | name | etc | cid,cid,cid....(list of courses)

courseA: cid | coursename | etc

If you wish to delete couse, don't. Instead mark it 'no longer available'. Now all your relationships will remain in tact.

[Waylander]

You dont really need to change anything,

All you have to do is where the logic inside your application for deleting the parent record is, you search the foreign key of the tables linked to it for child records (by using the primary key of the parent record).

You can set it to error and not delete or if you want it to cascade, you just make it delete the child records and then the parent.

Waylander.

[Chris_J_W]

...
Set your database up like this, then you can add or delete people at will.

people: pid | name | etc | cid,cid,cid....(list of courses)

courseA: cid | coursename | etc

....

I think you're not understanding what I mean by courses and sub-courses etc. Think of a course like a university/college degree, and inside that you have your subjects. Using this analogy, the subjects would be the sub-courses, and the degree would be the parent course.

Also, the courses probably will change a fair bit actually...oh, something else I didn't mention before - the actual data in the course tables currently is the status (ie "grades" for lack of a better word) of that person for each of the various sub-courses.

You dont really need to change anything,

All you have to do is where the logic inside your application for deleting the parent record is, you search the foreign key of the tables linked to it for child records (by using the primary key of the parent record).

You can set it to error and not delete or if you want it to cascade, you just make it delete the child records and then the parent.

Waylander.

I'm not entirely sure I know what you mean. It's in a PHP/MySQL system, and I use phpMyAdmin to manage the database. Would this basically be a case of writing a script for going through all the course tables manually and deleting any entries where the person's "id" matched that stored in the table? Or how do I actually make the relationships between primary/foreign keys etc exist? (for that matter, how do I define a foreign key through phpmyadmin?)

Thanks for the quick replies. Sorry if I'm not making sense on any of it. :D

[Chris_J_W]

I figured it out - it was actually embarrassingly simple :) Thanks for the attempts to help me by the way.

All I had to do was set up the "id" field on my course tables to be a foreign key referencing the "id" field on my people table, with "ON DELETE CASCADE" set. (and make them all InnoDB tables).

[Waylander]

I dont know anything about phpmyadmin im sorry to say...

If that program manages the data it should be able to handle it itself im assuming....

You will need to create the relationship if your going to let the app handle the data access, without looking at the entire model its a little unclear,

But what it think you need to do is in your course table you will need to somehow (I dunno how the app does it) define a column that will be linked to the primary key of the user table.

You might want to just ask in the php forum about relationships with phpmyadmin.

Waylander.

[Waylander]

Thats what I thought...

No worries,

Waylander.