ANSWERED: Re: how to have varying number of fields?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jennifer (and Josh and John and Cal), et al -- ...and then Jennifer Goodie said... % % Sounds a lot like the grape blends in different wines question posted last % week. :) Well, hully gee. I never knew songs and wine could have so much in common -- and church songs at that. Well, when I say it out loud it does make more sense ;-) % % You'll need a cross ref table with a one to many relationship and possibly a % field that says what the person did. % % like % | SongID | ComposerID | Role | % ++-++ % |1 | 1 | Poem | % |1 | 2 | Music | % |1 | 3 | Poem | % |1 | 1 | Lyrics | Ahhh... I get it. I don't have to point to the artist(s) from the songs table; I'll just point to the song and an artist as many times as necessary in this other table. Yet more adventures in normalization! Next I'll learn how to write a query that will return me Title Composer(s) Arranger(s) Author(s) ... Song Joe Bill Jane Other Jim Sue Bill AliceSam More Jane Joe Alice ready for display :-) % % % Just a suggestion, You might want more fields or different names. The proof of concept is what I needed. % % Search on last week's answers, I believe the thread was called Database I did; thanks. I've come away much enlightened. % Design or something like that. I'm sure there's not a lot of posts that % mention grapes and wines on the list. Yeah :-) Thanks! to all HAND mysql query, :-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+JMGUGb7uCXufRwARAjO4AJ9LMh2NhHsq+QrK4ih11uK6a1a50wCgwwcH IYkyIYPXOHs/PsF0SODPAzs= =/et/ -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
RE: how to have varying number of fields?
Sounds a lot like the grape blends in different wines question posted last week. :) You'll need a cross ref table with a one to many relationship and possibly a field that says what the person did. like | SongID | ComposerID | Role | ++-++ |1 | 1 | Poem | |1 | 2 | Music | |1 | 3 | Poem | |1 | 1 | Lyrics | Just a suggestion, You might want more fields or different names. Search on last week's answers, I believe the thread was called Database Design or something like that. I'm sure there's not a lot of posts that mention grapes and wines on the list. -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 14, 2003 3: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
RE: how to have varying number of fields?
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 roleIDFK === === 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
