Having designed this database 4-5 times over the past 10 years, I will share with you how I did it.
I have 4 tables for this product --- productID === title === person --- personID === name === role --- roleID === description === product_person --- productID FK personID FK roleID FK === === Then for any given product I can store an unlimited number of people who participated in creating it. (http://www.jjonline.com/catalog/productPage.php?productID=16459) This particular product had 5 people who played different roles. Each person can play 1:n roles. Obviously, I left off a lot of the detail in the table structures. But you should be able to get the general idea form it. HTH, =C= * * Cal Evans * Stay plugged into your audience. * http://www.christianperformer.com * -----Original Message----- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 5:10 PM To: mysql users Subject: how to have varying number of fields? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, all -- The Subject: may be a bit misleading, so let me start from scratch. I'm creating a sheet music catalog for our church, and one of the things I'll want to track is who wrote it. [Actually, we have music composers and arrangers and poet authors as well as particular biblical sources, but I think that the first three can be classed together.] Of course, Martin Broones does a lot of the music, and Avelyn Kerr a lot, and I wouldn't want to repeat that, so of course I need a 'composers' table (yeah, it should probably be a more rounded name, but so what :-) The problem comes when I have a piece of music whose words or music are by two people, such as "Music by Martin Broones. Poem by John and Jane Doe." -- when I already have entries for John and Jane on their own. Do I just bite the bullet and make another record for "John and Jane", filling in their names and dates and styles and other 'composers' fields in duplicate, or is there a way to have my 'pieces' table, listing all of the pieces and their attributes, somehow point to *both* of those composers in the foreign 'composer' field? TIA & HAND Good grief; even Dan's qsecretary is better than this mysql query thing! :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health" http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+JJi7Gb7uCXufRwARAqkaAKCquKk01B5NeAngFUXir6bIMJAPkgCeI0zk YnJGqiKl6+i36SXa9IwQvCU= =bxf7 -----END PGP SIGNATURE----- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
