visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: Linking multiple rows of one table to a record
By: Squibi U.S.A.  Date: 08/09/2003 00:00:00  English  Points: 125 Status: Answered
Quality : Excellent
I am trying to design a database for a school in which 'outcomes' (what the school wants their kids to learn) are assigned to 'units of study'. If there was a one to one relationship between the two, i could just make a field in the 'unit' table that contained the primary key of the 'outcome' assigned to it. Problem is, in one 'unit' there may be several 'outcomes'. How would i go about being able to add a list of 'outcomes' to a single 'unit'. It needs to be possible to change the list - add or remove 'outcomes' (i will be referencing the table from PHP). Any ideas?
By: RanjeetRain Date: 08/09/2003 00:40:00 English  Type : Comment
You can create a parent child relationship. In relational world, this is referred to as Primary-Foreign key relationship.
By: RanjeetRain Date: 08/09/2003 00:50:00 English  Type : Assist
Say you have a table called "Units_of_study" and another called "Outcomes". What you may do is, you may have one field in each table which will act as a key. And then that key will help you identify the parent record from the child table.
By: VGR Date: 08/09/2003 01:00:00 English  Type : Answer
without any automatic referential integrity mechanism, all you ask for is a relation, right ? A relation becomes a table according to M. Codd.

Thus you have :

'units of study' table with id PRI KEY ("integer unique auto_increment" is the same as "pri key")
'outcome' table with id (idem to the above)
'study_outcome' table making the relation :
CREATE TABLE study_outcome (id integer unique auto_increment,outcome_id integer not null, study_id integer not null);

that's all.

to get the "outcomes" of a "unit" having id=$id1 :
select outcome_id from study_outcome where study_id=$id1;

I think you got the picture
By: Squibi Date: 08/09/2003 06:31:00 English  Type : Comment
Thanks for that - as usual the problem was just that i was looking at it the wrong way. I've now gone and found myself more info on SQL joins, which seems to help even more. Thanks!

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 297.809840 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page