[Zope] Linking two tables

[Rob Page]

> This question might be slightly off topic. But anywhere here it is:
> 
> I have two tables in my MS Access database:
> 1. Containg a record for each employee
> 
> 2. DVD database containing a record for each DVD
> 
> What I would like to do with Zope, is to somehow link these two
> together, so that I can write on my intranet that  Mr. X has DVD this
> and that.
> 
> My problem is that I can offcause make a field in the first table
> (employee table), where I list the IDnumbers of the DVDs (seperated by
> commas), which the employee owns, but then a query returns these id's
> comma seperated and I don't how to handle this (in a <dtml-in> -loop).

You could create a third associative table (dvd_status) with:

o  its own primary key
o  a foreign key into the employee table
o  a foreign key into the DVD table

<untested sql>

  SELECT employees.first_name, dvds.dvd_name
  FROM employees, dvds
  WHERE 
  employees.employee_id = dvd_status.employee_id AND dvd_status.dvd_id =
dvds.dvd_id

</untested sql>

--Rob