Justtechjobs.com Find a programming school near you






Online Campus Both


php-general | 2001062

[PHP] RE: [PHP-DB] Design conundrum... From: Kristian Duske (ml.duske <email protected>)
Date: 06/23/01

> The problem involves 2 tables, one of editors and one of categories, both
> have unique ID numbers. Each editor can have authority over an arbitrary
> number of categories, and conversely, each category can have an arbitrary
> number of editors. So what is the best way to represent this in the DB?
> (Which is PostgreSQL 7 BTW)

This is a so-called N:N relation, and this usually calls for a third table
to store the cross-relations:

table editors
id, name, email

table categories
id, name

table ediors_categories
editor (id of editor)
category (id of category)

Now if you want to give an editor authority over a category, you would
insert a new record into editors_categories with the respective ids of the
editor and the category.
If you want to select all categories that belong to a certain author, you
would do this:
SELECT c.* FROM categories c LEFT JOIN editors_categories ed ON c.id =
ed.category WHERE ed.editor = '" . $editor_id . "'"

This selects all category records and joins them with the editors_categories
table, then filtering the resulting recordset by the editor id stored in the
editors_categories table.

Hope this helps
Kristian 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-general-unsubscribe <email protected>
For additional commands, e-mail: php-general-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>