Re: when to normalize out to a table
David T-G wrote: Well, I do, though I'm not surprised that I shouldn't. I still don't know anything about left, right, inner, and outer joins; they sound like belly buttons to me :-) The best thing to do is find a website with some sample data and sample queries and see how they actually work yourself. The 'direction' of a join simply refers to the tables in the query itself, assuming its written left to right; so a LEFT join joins the table to the RIGHT of it to the one to the LEFT of it, using data from the LEFT one and then finding the matching data (from the ON) in the RIGHT one. Reverse RIGHT, LEFT above for RIGHT JOIN. The others will make more sense when you get that. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: when to normalize out to a table
Cal Evans wrote: Always start with a *fully* normalized design. You can always denormalize for speed but it's real hard to normalize once you are in production. I'd rather be 'stupid' about how normalized my data is and then write caching into my program (or use MySQL 4's query cache, which I don't have yet) than have to extract data properly after the fact for normalization. Reading any old website about normalization will say basically the same thing (and if they don't, they shouldn't be writing about normalization). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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
when to normalize out to a table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I wonder if there's a best-practice line of when to bother to normalize data out to a table. Certainly I wouldn't want to have a table of integers and have a column in another table point into it :-) and almost certainly I'd want to point out to a table of personnel rather than list the info -- even just the name -- in the table of other info. Well, for the music catalog I've started I have to track the vocal range of the piece. I only have five values (high, medium high, medium, medium low, low) and I could at least use 5 logical abbreviations (HH, MH, MM, ML, LL), and I might even come up with 5 single-letter abbreviations that would thus be exactly as efficient as going out to another table via a smallint pointer -- and yet I wouldn't ever have to dereference to see what range I meant. How does one decide what to do in a grey area of this sort -- in this situation, for example? ;-) TIA 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+JKHwGb7uCXufRwARApErAJ4r4DyzeSJVt4+/I7H7isVd77zUQQCg0igD vabIu29v+Jm5s0HOMaGnsLE= =MRq6 -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: when to normalize out to a table
IMHO, etc. Always start with a *fully* (ok, by fully I mean 3rd normal form...mainly because I still don't fully understand 4th and don't even pretend to understand 5th) normalized design. You can always denormalize for speed but it's real hard to normalize once you are in production. Vocal range deserves a 'domainTable' of it's own. vocalRangeType --- vocalRangeTypeID === description === product then has vocalRangeTypeID as a FK. Nothing wrong with a table that is just a bunch of FKs to other tables. YMMV, 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:49 PM To: mysql users Subject: when to normalize out to a table -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, all -- I wonder if there's a best-practice line of when to bother to normalize data out to a table. Certainly I wouldn't want to have a table of integers and have a column in another table point into it :-) and almost certainly I'd want to point out to a table of personnel rather than list the info -- even just the name -- in the table of other info. Well, for the music catalog I've started I have to track the vocal range of the piece. I only have five values (high, medium high, medium, medium low, low) and I could at least use 5 logical abbreviations (HH, MH, MM, ML, LL), and I might even come up with 5 single-letter abbreviations that would thus be exactly as efficient as going out to another table via a smallint pointer -- and yet I wouldn't ever have to dereference to see what range I meant. How does one decide what to do in a grey area of this sort -- in this situation, for example? ;-) TIA 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+JKHwGb7uCXufRwARApErAJ4r4DyzeSJVt4+/I7H7isVd77zUQQCg0igD vabIu29v+Jm5s0HOMaGnsLE= =MRq6 -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: when to normalize out to a table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cal, et al -- ...and then Cal Evans said... % % IMHO, etc. *grin* % % Always start with a *fully* (ok, by fully I mean 3rd normal form...mainly % because I still don't fully understand 4th and don't even pretend to Yeah; I'm in the same boat :-) % understand 5th) normalized design. You can always denormalize for speed but % it's real hard to normalize once you are in production. Well, good point. % % Vocal range deserves a 'domainTable' of it's own. Really? Well, OK; I can, as you say, start more normal and then change (does that mean I morph, mutate, or mogrify? :-) % % vocalRangeType % --- % vocalRangeTypeID % === % description % === % % product then has vocalRangeTypeID as a FK. Right. That means another lookup, but it would certainly work. % % Nothing wrong with a table that is just a bunch of FKs to other tables. Oh, I agree :-) % % YMMV, HTH, % =C= Thanks 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+JL8CGb7uCXufRwARAnkAAKDipXtjkqL9lkmY3s2LvrWTTLAubACg3OIN Uz9TVmyblAhy7BbwryF2KEM= =isNN -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: when to normalize out to a table
% % vocalRangeType % --- % vocalRangeTypeID % === % description % === % % product then has vocalRangeTypeID as a FK. Right. That means another lookup, but it would certainly work. If by lookup you mean another trip to the database, not necessarily, left join this table to your product select statement and bring in all the data at once. =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 7:53 PM To: mysql users Cc: Cal Evans Subject: Re: when to normalize out to a table - 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: when to normalize out to a table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cal, et al -- ...and then Cal Evans said... % ... % % product then has vocalRangeTypeID as a FK. % % Right. That means another lookup, but it would certainly work. % % If by lookup you mean another trip to the database, not necessarily, left % join this table to your product select statement and bring in all the data % at once. Well, I do, though I'm not surprised that I shouldn't. I still don't know anything about left, right, inner, and outer joins; they sound like belly buttons to me :-) I've done some reading on joins and haven't figured it out yet. Pointers to favorite tutorials are appreciated, but I'm trying not to flat-out ask the list to explain it to me (though that's fine with me at this point, too). % % =C= Thanks again 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+JMSNGb7uCXufRwARAhDlAJ4mEQfLWxMPs+liVE/aUTNs08dwcgCfeRNT FowEnZRXzwiyBtkjLXGbaK4= =mi3l -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: when to normalize out to a table
http://froogle.google.com/froogle?q=sql+for+dummiesbtnG=Froogle+Search http://froogle.google.com/froogle?q=sql+for+smartiesbtnG=Froogle+Search http://froogle.google.com/froogle?q=mysql+paul+duboisbtnG=Froogle+Search (first book in the list) I can personally recommend the first two and while I've never met him, I do not hesitate to recommend the author of the third. :) These are all portable, energy efficient and almost never crash unless thrown. Also, using the table structures we've discussed, something like: Select p.productID, p.title, v.description as range from product p left join voicingType v on p.voicingTypeID = v.voicingTypeID where productID = 4 should get you a result set back with a productID, title and the description of the range. =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 8:17 PM To: mysql users Cc: Cal Evans Subject: Re: when to normalize out to a table -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cal, et al -- ...and then Cal Evans said... % ... % % product then has vocalRangeTypeID as a FK. % % Right. That means another lookup, but it would certainly work. % % If by lookup you mean another trip to the database, not necessarily, left % join this table to your product select statement and bring in all the data % at once. Well, I do, though I'm not surprised that I shouldn't. I still don't know anything about left, right, inner, and outer joins; they sound like belly buttons to me :-) I've done some reading on joins and haven't figured it out yet. Pointers to favorite tutorials are appreciated, but I'm trying not to flat-out ask the list to explain it to me (though that's fine with me at this point, too). % % =C= Thanks again 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+JMSNGb7uCXufRwARAhDlAJ4mEQfLWxMPs+liVE/aUTNs08dwcgCfeRNT FowEnZRXzwiyBtkjLXGbaK4= =mi3l -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: when to normalize out to a table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Cal, et al -- ...and then Cal Evans said... % % http://froogle.google.com/froogle?q=sql+for+dummiesbtnG=Froogle+Search % http://froogle.google.com/froogle?q=sql+for+smartiesbtnG=Froogle+Search % http://froogle.google.com/froogle?q=mysql+paul+duboisbtnG=Froogle+Search % (first book in the list) % % I can personally recommend the first two and while I've never met him, I do % not hesitate to recommend the author of the third. :) I've heard good things about the third (well, *lots* of good things; I've heard at least *some* good things about all three) and it's on my list as soon as I have some cash to buy it. % % These are all portable, energy efficient and almost never crash unless % thrown. *grin* % % Also, using the table structures we've discussed, something like: % % Select p.productID, %p.title, %v.description as range % from product p left join voicingType v on p.voicingTypeID = % v.voicingTypeID % where productID = 4 % % should get you a result set back with a productID, title and the description % of the range. OK. First I have to get my head around your table structure, which I'll do in more detail now that I have an example against it, and then I have to figure out how to read that to learn a bit about how it works -- but I'm very grateful! % % =C= % % * % * Cal Evans % * Stay plugged into your audience. % * http://www.christianperformer.com Thanks HAND :-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+JNvxGb7uCXufRwARAjZvAJ9L0KsXMh/agnA+ITYGiVUb5zysegCgz5Bi Yye0dODpRPKB5MmsdViAm04= =FGyI -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
