Well... thanks Cal & Day. Your thoughts are brillant. It makes me very proud that more experienced DB designers think I'm doing great.
About the normalization rules, I have them inside the "PHP and MySQL Web Development" book, written by Luke Welling and Laura Thomson, but since my native language is Spanish, it's very hard for me to keep up with understanding after reading 200 pages in a row (kinda in a hurry here ;-) Well, thanks for your help and for letting me know that I can count on all of you to make my life easier. César Aracena IS / MCSE+I Neuquén, NQN (0299) 156-356688 (0299) 446-6621 > -----Mensaje original----- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Enviado el: Lunes, 10 de Junio de 2002 11:57 p.m. > Para: César L. Aracena; MySQL General > Asunto: Re: Here's something > > Wow, > > for your first shot, you've got a very normalized database. GOOD JOB! > > The only thing wrong is your payments table. > > It needs to be more like: > > payment > ----- > payment_id > ===== > class_id > user_id > date > amount > ----- > > > ----- > (The primary key is the combination of class_id and userID) > > Then you could : > * join it with the classes table to see how much each class brought in, > * Join it with the users table to see how much each person has paid > * join it with the classes to get a roster of attendees. > * join it with the classes where amount is null to get a list of > deadbeats. > * join it with teachers and classes to see how much each teacher is > grossing. > > You get the idea. > > A couple of rules I always go by: (They are my rules, others may debate > them > but so far nobody has convinced me to change them...you'll come up with > your > on as you go on) > * All tables have a primary key of <tableName>ID (or in your case > <tableName>_id...but I hate [_]!) > * Table names should be singular. > this way a person does not have a people_id...he/she has a person_id. :) > > Go hit google.com and search for the 5 rules of data normalization. You > are > already doing most of the first 3 now but they will help you formalize > what > you are doing. (and 4 & 5 are not used that much, concentrate on 1-3) > > Keep up the good work! > > =C= > > * > * Cal Evans > * Techno-Mage > * http://www.calevans.com > * > > ----- Original Message ----- > From: "César L. Aracena" <[EMAIL PROTECTED]> > To: "MySQL General" <[EMAIL PROTECTED]> > Sent: Monday, June 10, 2002 9:23 PM > Subject: Here's something > > > Hi all. This is my first post here, so please be patient with me. > > I don't know if there's a program already made that does what I need, so > I don't have to "reinvent the wheel". I'm using PHP & MySQL to make most > of the web sites and web-based Corporate Intranet's programs. What I > need now, is to make a program that can keep track of a social club > affiliates and their monthly payments. Also keep track of the teachers > and classes hours. Because I'm taking my first steps into RDBM's, I > would appreciate very much all the help I can get in order to create the > perfect DB structure. > > What I have in mind, is something like this: > > Table users (user_id, affil_number, last_name, first_name, etc.) > > Table teachers (teach_id, last_name, first_name, etc.) > > Table classes (class_id, name, hours) > > Table teach_clases (class_id, teach_id) > > Table payments (payment_id, user_id, month, year, main_fee, class1_fee, > class2_fee, etc.) > > What I need to do is show, as an example, the total payments made by > affiliate # 0345 in the last 12 months. Other example would be to show > all the persons who hasn't paid last month fee. > > Is what I'm thinking correct or should I consider doing some other table > or perhaps changing one that's already made? > > Thanks in advance, > > Cesar Aracena > [EMAIL PROTECTED] > CE / MCSE+I > Neuquen, Argentina > +54.299.6356688 > +54.299.4466621 > > > > --------------------------------------------------------------------- > 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 <mysql-unsubscribe- > [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 <mysql-unsubscribe- > [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
