RE: [PHP-DB] Design conundrum...
> 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]
Re: [PHP-DB] Design conundrum...
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...
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...
> 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]
[PHP-DB] Design conundrum...
I'm trying to figure out the most elegant way to solve my database design problem and I thought I pick some at the brains on this list. 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) Right now I am considering using an array in one of the tables but I have heard that walking the array to check values is both slow and somewhat cumbersome. Is there a good way to do this with a third table maybe? Please don't hesitate to point out my stupidity if the simplicity of the solution indicates it. :) Thanks, -Jesse -- 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]
