List:General Discussion« Previous MessageNext Message »
From:Gordon Date:May 24 2005 6:39pm
Subject:RE: DB design question
View as plain text  
You probably want to add type to both the address and phone tables. Then you
can be selective in your reporting and still get 1 row per student in your
result set. Just remember if your data has the possibility of not having the
information for a student you want to use LEFT JOIN's vs INNER JOIN's or the
student with no "primary" phone [in the following statement] will not be
included in the result set.

SELECT student_id, 
       name, 
       age,  
       h.street_name AS home_address, 
       s.street name AS school_address,
       n.num AS primary_phone
FROM   student s
       LEFT JOIN address s 
       USING (student_id) 
       LEFT JOIN address h 
       USING (student_id)
       INNER JOIN phone_num n
       USING (student_id)
WHERE  h.type = 'Home' 
       AND s.type = 'School'
       AND n.type = 'Primary'

-----Original Message-----
From: Koon Yue Lam [mailto:kisstech@stripped] 
Sent: Tuesday, May 24, 2005 12: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


Thread
DB design questionKoon Yue Lam24 May
  • RE: DB design questionGordon24 May
RE: DB design questionBob)24 May
  • Re: DB design questionMartijn Tonies24 May
    • Re: DB design questionSGreen24 May
      • Re: DB design questionMartijn Tonies24 May
RE: DB design questionMikhail Berman24 May
RE: DB design questionMike Johnson24 May
RE: DB design questionMike Johnson24 May