RE: [PHP-DB] Design conundrum...

2001-06-23 Thread Kristian Duske

 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 editors_categories ec LEFT JOIN categories c 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 Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Design conundrum...

2001-06-23 Thread Antonio Mármol Albert

El sábado 23 de junio de 2001  (13:52), Kristian Duske escribió:

  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)

With index in the third table, How would it be ?

PRIMARY KEY (editor, category)

 or

KEY (editor),
KEY (category)

What's the better (more efficient and correct) ??

Regards
-- 
Antonio Mármol Albert ( [EMAIL PROTECTED] )
http://www.infurma.es

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




Re: [PHP-DB] Design conundrum...

2001-06-23 Thread Stephen van Egmond

Antonio M?rmol Albert ([EMAIL PROTECTED]) wrote:
 With index in the third table, How would it be ?

It depends on the dynamics of the application.

1) If you have to ask what cateogories does the editor edit?, you
create an index on editor.

2) If you have toa sk what editors does this category have?, you
create an index on category.

For correctness (but not efficiency) a unique index on (editor,
category) would be needed.

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]




RE: [PHP-DB] Design conundrum...

2001-06-23 Thread Kristian Duske

 With index in the third table, How would it be ?

 PRIMARY KEY (editor, category)

  or

 KEY (editor),
 KEY (category)

 What's the better (more efficient and correct) ??

I don't really know - I personally use a third id field as a primary key,
but if you want to use REPLACE to update / insert your records, you have to
use the PRIMARY KEY (editor, category) version - your the fields you mention
in your REPLACE statement must form a unique key.

Kristian


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]