List:General Discussion« Previous MessageNext Message »
From:Mike Johnson Date:May 24 2005 6:02pm
Subject:RE: DB design question
View as plain text  
From: Koon Yue Lam [mailto:kisstech@stripped] 

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

It's good DB design, but you need to not `select *' but the specific
fields you'd like.

An example of might be:

SELECT s.name, s.age, a.street_name, n.num, n.extension 
FROM students s 
JOIN address a ON a.student_id = s.student_id 
JOIN phone_num n ON n.student_id = s.student_id 

HTH!


-- 
Mike Johnson             Smarter Living, Inc.
Web Developer            www.smartertravel.com
mike@stripped   (617) 886-5539

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