Something like this would make more sense to me and provide greater flexibility;
student ---------------- student_id name age address --------------- address_id street_name city state zip phone_num ------------------ phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) student_info ------------------- student_id_FK phone_num_id_FK address_id_FK Spent all of 10 mins on this so its not perfect. Bottom line is I would not include the student_id in the address and phone tables. It precludes a student having multiple phones or addresses with out duplicate data. The addition of the student_info table provide the 1:1 or 1:N mapping you're looking for I believe. The only thing you need to ensure is properly set the Cascade on update and restrict on delete options to ensure data integrity. My gut tells me it may be a better implementation to map the student/phone and student/address separately and then create the student_info using keys from these intermediate tables, but it more complicated and it not clear what the constraints on your problem is. Bob Bartis -----Original Message----- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 1:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student ---------------- student_id name age address --------------- address_id student_id street_name phone_num ------------------ student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
