Beedrew
3-17-06, 09:45 PM
Hello,
I am quite new to mySQL. I have read several articles on data normalization and would like to get my tables set up for the most efficient querying.
I have a table for book TITLES and a table for book CREATORS. Creators might include authors, editors, illustrators, etc.
Since there is a many-to-many relationship between TITLES and CREATORS, I need relational table(s) with columns for TITLE_ID and CREATOR_ID. Somehow, I also need to get at the "action" that a certain creator took for a certain title -- e.g., was the title "by" that creator, "edited by" that creator, "illustrated by" that creator, etc.
I have imagined two ways I could do this:
1. Have separate relational tables depending on the action -- e.g., I could have a relational table containing TITLE_ID and CREATOR_ID for authors, another one for editors, and another one for illustrators. I'd have to query all 3 tables looking for a certain title_id in order to print something like, ""Book Title, by Jane Smith and John Jones, illustrated by Jack Taylor."
2. Have a single relational table that matches TITLE_ID and CREATOR_ID with a third column for ACTION. This column could contain "B" for "by," "E" for "edited by", and "I" for "illustrated by." To build a line like I wrote above, I'd have to find all the instances of a certain TITLE_ID and handle any B's, then E's, then I's. The script would be more complicated that the other option.
Basically, Option 1 would give me a series of queries against different tables and a relatively painless script...while Option 2 would make for a single query against one table, but a more complicated script.
Other queries have to be considered as well -- for instance, when searching for TITLE by CREATOR, that query would have to accept a CREATOR_ID and then go look for matching TITLE_IDs...in each of the 3 tables, or in the single table.
I'm looking for opinions from more experienced programmers about which option would make for faster queries and page rendering.
Thanks for any feedback,
Bee
I am quite new to mySQL. I have read several articles on data normalization and would like to get my tables set up for the most efficient querying.
I have a table for book TITLES and a table for book CREATORS. Creators might include authors, editors, illustrators, etc.
Since there is a many-to-many relationship between TITLES and CREATORS, I need relational table(s) with columns for TITLE_ID and CREATOR_ID. Somehow, I also need to get at the "action" that a certain creator took for a certain title -- e.g., was the title "by" that creator, "edited by" that creator, "illustrated by" that creator, etc.
I have imagined two ways I could do this:
1. Have separate relational tables depending on the action -- e.g., I could have a relational table containing TITLE_ID and CREATOR_ID for authors, another one for editors, and another one for illustrators. I'd have to query all 3 tables looking for a certain title_id in order to print something like, ""Book Title, by Jane Smith and John Jones, illustrated by Jack Taylor."
2. Have a single relational table that matches TITLE_ID and CREATOR_ID with a third column for ACTION. This column could contain "B" for "by," "E" for "edited by", and "I" for "illustrated by." To build a line like I wrote above, I'd have to find all the instances of a certain TITLE_ID and handle any B's, then E's, then I's. The script would be more complicated that the other option.
Basically, Option 1 would give me a series of queries against different tables and a relatively painless script...while Option 2 would make for a single query against one table, but a more complicated script.
Other queries have to be considered as well -- for instance, when searching for TITLE by CREATOR, that query would have to accept a CREATOR_ID and then go look for matching TITLE_IDs...in each of the 3 tables, or in the single table.
I'm looking for opinions from more experienced programmers about which option would make for faster queries and page rendering.
Thanks for any feedback,
Bee