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 <[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
