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:kisstech@stripped]
Sent: Tuesday, May 24, 2005 1:34 PM
To: mysql@stripped
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
