Bes/Alternative Solutions please?

condoug

I am looking to establish what the best/alternative routes would be to achieve the following, or if in fact what i am doing is what other developers also do:

I have a table of unique records which can be linked to multiple records from a second table (so a one to many relationship in effect).

For explanation sake, table 1 is called 'Schools' and table 2 is called 'pupils'.

At the moment, I store the Unique ID's (primary keys) of the 'pupil' records in a varchar field within the 'school' record with spaces separating them as such..

1 2 4 10 12 14

.. to indentify which pupils are assigned to which school.

And when I come to display the records on screen, I run a query to get the 'schools' record data and explode the varchar field into an array:

$pupil_ids = explode(" ", $varcharfield);

and then run a second query to get everything from the 'pupils' table and if the unique id's of the 'pupils' is in the array $pupil_ids, I display the record on the web page.

So, is this a familiar routine that other use, or is there alternative routes to achieve my end result?

bare in mind, from an admin point of view, this needs to be updateable and at the moment i use check boxes to check which 'pupil's are assigned to which 'school' and run an update query to update the Varchar field.

Many thanks for reading.

Doug

Rabbie

The normal way to implement a one to many relationship would be to store the Primary Key of the school in the Pupil record. Then you can query the pupil records for all pupils at a particular school.

Weedpacket

condoug wrote:
So, is this a familiar routine that other use, or is there alternative routes to achieve my end result?

The usual practice is to have a table to express each relation (hence, "relational database"). In this case the relationship is between pupils and schools, so as well as a table of Pupils, and a table of Schools, there is a third table that lists which pupils are in which schools.

Then the task of retrieving the records for all pupils at a particular school is one query instead of two queries with a string-array conversion in between.

condoug

Hi Weedpacket

I understand your route and only have one question off the back of it.

In my admin system, I display some web page content and this is where my relationship comes into play. Each web page can have multiple documents attached to it (these are my pupils!).

So, I tick which documents (from a big long list) I want to attach to this web page content. For each check box ticked, I insert a record into the '3rd' table storing the web page id and the document id.

My questions is - How would I delete that record if the user wishes to remove the document because as far as I am aware, you cannot delete a database record based on a checkbox that has been un-ticked.

I hope that makes sense.

Thanks for your help and support.

Weedpacket

That sounds like a completely different question; but

condoug wrote:
you cannot delete a database record based on a checkbox that has been un-ticked

You certainly can. All you need to do is figure out which checkboxes weren't ticked. You can do that by determining which checkboxes were displayed and removing all those that were ticked. That will leave the ones that weren't.

But why would you need to delete records? The description of your problem only mentions adding them.