[RESOLVED] Database design - one to many relationships

[Tezread]

I think im starting this thread to clarify whether my database design is correct but mort importantly to establish a correct query.

I have the following tables

organisation
orgid
address1
etc

activity
activityid
activity

expertise
expertid
expertise

Basically and organisation can do one or more activities and areas of expertise - simple right ....one to many.

Now this is where im wanting to clarify. I want to be able to store the activities/expertise in other tables and i thought I would do it by having two tables which stored the multiple activities/areas of expertise an organisation participates in....

activity_org
activityorg_id
orgid
activityid

and...

expert_org
expertorg_id
orgid
expertid

so activity_org my look like this: http://www.mindseyemidlands.co.uk/actorg.gif

so organisation id 3 does activities with an id of 8,12 and 25 etc

I have created a form which lists all the activity/experts and you can choose which ones you want.

my questions then are

a) is this database design correct and if not what do i do to make it good?
b) What will my query look like if I want to list the organisations that do those activities/areas of expertise as chosen in the form.

thankyou very much fopr listening

[laserlight]

The extra tables would be used in a many-many relationship. This could very well be the case since a given organisation would have many activities and areas of expertise, but different organisations might have the same activity and area of expertise.

[ahundiak]

As laserlight has said, your data model has many-to-many relations. Your table layout is fine though technically you don't really need the expertorg_id column since the combo of expertid and orgid will be unique. But that's a question os taste.

You will probably want to do one query to get activities and one for expertise. If you try to combine the queries into one then you will encounter a multipilier effect i.e. if any organization has 4 activities and 5 areas of expertise then you would get back 4 x 5 = 20 records.

So use a simple query such as
SELECT
organization.address1 AS org_address1,
expertise.expertise AS expertise
FROM
organization
LEFT JOIN expert_org ON expert_org.orgid = organization.orgid
LEFT JOIN expertise ON expert_org.expertid = expertise.expertid
WHERE
organization.orgid = 27;

[Tezread]

Cool. So in this case my query will look something like this:

"SELECT qualityadmin.quality_name AS quality_name, activity.activity AS activity FROM qualityadmin
LEFT JOIN activity_org ON activity_org.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activity ON activity_org.activity_id = activity.activity_id
WHERE
qualityadmin.qualityadmin_id = 27";

but the above is wrong in that:

WHERE
qualityadmin.qualityadmin_id = 27";

does not fetch the results i need
my next question then is how would this query fit in the context of my form where the user picks X number of activities. I know these activity choices should be put into an array but how will the query above be changed to see user choices that match.- here is what I have at the moment

	  include("connect.php");
	if(isset($_GET['submit'])){

// Start the query 
"SELECT qualityadmin.quality_name AS quality_name, activity.activity AS activity FROM qualityadmin
LEFT JOIN activity_org ON activity_org.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activity ON activity_org.activity_id = activity.activity_id
WHERE
qualityadmin.qualityadmin_id = 27";

and this here is my array syntax to check the activity_checkbox array for passed values, build a query line for them if there are any

if(isset($_GET['activity_checkbox']) && (count($_GET['activity_checkbox']) > 0)) {

   $act_sql_append =  " AND activity.activity_id IN ('" . implode("','", $_GET['activity_checkbox']) . "')"; 

  $sql_admin .= $act_sql_append; 
} 


// Run the query, and fetch the results 
$sql_result = mysql_query($sql_admin) or die("Error in search query: ".mysql_error()); 
$sql_array = mysql_fetch_assoc($sql_result); 

// Print your results here 

print_r($sql_array);

} 

?>

[ahundiak]

Are you trying to find all organizations that match the activities? If so then "reverse" the query such that you are selecting from the activity table and then joining the org table.
SELECT whatever
FROM activity
LEFT JOIN activity_org ...
LEFT JOIN organization
WHERE
activity.activity_id IN (...)

[Tezread]

Yep - im trying to find all organisations that participate in an array of activities as chosen by the user in this form:

// Get the activities 
$sql_activity = "SELECT * FROM activity ORDER by activity_id"; 
$activity_result = mysql_query($sql_activity) or die("Error fetching activities: ".mysql_error()); 
$activity_array = mysql_fetch_assoc($activity_result); 

  echo '<form name="searchform" method="GET" action="infolinks_choicesv2_formhandler.php">'; 
echo '
<table> 
<tr> 
<td>Select the activities:</td> 
</tr> 
<tr> 
<td>'; 
while ($row = mysql_fetch_assoc($activity_result)) { 
//foreach($activity_array as $activity){ 
echo '<input type="checkbox" name="activity_checkbox[]" value="'.$row['activity_id'].'">'.$row['activity'].'';
} 

Im sorry for being thick but I still don't get how a LEFT JOIN works. I have been working on the query to list all the organisations that do the activities chosen in the form above but I just cannot get my head around it. The manual is just confusing and Google/my books are sending me loopier. I have noticed in other examples that they are not dealing with 3 tables.
Is this not the same as doing a join?

"SELECT qualityadmin.qualityname,  activity.activity_id, activity_org.activity_id
FROM qualityadmin, activity , activity_org
WHERE activity.activity_id = activity_org.activity_id  ORDER BY quality_name";

[ahundiak]

Using WHERE is a type of JOIN epecifically the INNER JOIN. Joins can be confusing. Just need to work through some tutorials to understand the differences.

I generally like to use JOINS to build up composite records and use WHERE to explicitly select which if the composite records I want. I think JOINs are more readable and more flexible than WHERE conditions once you get used to them. But if WHERE works for you then have at it.

[Tezread]

Are you trying to find all organizations that match the activities? If so then "reverse" the query such that you are selecting from the activity table and then joining the org table.
SELECT whatever
FROM activity
LEFT JOIN activity_org ...
LEFT JOIN organization
WHERE
activity.activity_id IN (...)

im wondering- how come there is only as reference to a single table when doing the select statement when there are 3 tables involved in the process? You are right that I want to find all organizations that match the activities? If I have to reverse the query such that I am are selecting from the activity table and then joining the org table - is this correct?

"SELECT * FROM activity.activity_id AS activity_id, activity.activity AS activity,
organisation.org_id AS org_id
FROM activity, organisation
LEFT JOIN activityrelations ON activity.activity_id = activityrelations.activity_id
LEFT JOIN organisation ON activityrelations.otg_id = organisation.org_id
WHERE activity.activity_id = 27"

[Tezread]

Just an update for those who may find this thread useful in future-

ive tried the following LEFT JOIN query in phpmyadmin and it does give an output that makes sense - i.e giving the activity_id, activity, quality_id relating to it and the username that chose the activity in the first place.

SELECT activity.activity_id AS activity_id, activity.activity AS activity,
qualityadmin.qualityadmin_id AS qualityadmin_id, activityuser.username AS username
FROM activity
LEFT JOIN activityrelations ON activity.activity_id = activityrelations.activity_id
LEFT JOIN qualityadmin ON activityrelations.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activityuser ON activity.activity_id = activityuser.activity_id
WHERE activityuser.username = 'administrator';

[Tezread]

i tried applying this query (which works in phpmyadmin) to my PHP forms but the result is not processing and so nothing gets echoed:

if (isset($_POST['submit'])) { 

$sql = "SELECT activity.activity_id AS activity_id, activity.activity AS activity,
qualityadmin.qualityadmin_id AS qualityadmin_id, activityuser.username AS username 
FROM activity
LEFT JOIN activityrelations ON activity.activity_id = activityrelations.activity_id
LEFT JOIN qualityadmin ON activityrelations.qualityadmin_id = qualityadmin.qualityadmin_id
LEFT JOIN activityuser ON activity.activity_id = activityuser.activity_id
WHERE activityuser.username = '$username'";

if(!$sql) die("SQL ERROR: ".mysql_error()); 
$result = mysql_query($sql) or die(mysql_error()); 

} 


$row = mysql_fetch_array($result);

    echo "<tr><td width=\"20%\" class=\"gto8\">".($row['quality_name'])."</td><td width=\"33%\" class=\"gto8\">".($row['quality_id'])." ".($row['username'])."</td>";