List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:May 24 2005 7:35pm
Subject:Re: DB design question
View as plain text  
Shawn,

I agree with you that the tables can have different info with regard to
the requirements.

But for storing only addresses for specific students, this 4 table design
seems weirdish to me... I think it makes more sense to keep a
student_id in the Addresses table...

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com

  > 
  > > Something like this would make more sense to me and provide greater
  > flexibility;
  > 
  > It doesn't to me...
  > 
  > > student
  > > ----------------
  > > student_id
  > > name
  > > age
  > >
  > > address
  > > ---------------
  > > address_id
  > > street_name
  > > city
  > > state
  > > zip
  > 
  > What addresses are these? Random addresses where a student _might_ live?

  Not necessarily random but yes, those would be addresses. 

  > 
  > > phone_num
  > > ------------------
  > > phone_num_id
  > > num
  > > extension
  > > type (cell, home, etc)
  > > primaryNumber (yes/no)
  > 
  > Again, random phone numbers possibily owned by a student?

  Yes. Again, not necessarily random. 

  > 
  > >
  > > student_info
  > > -------------------
  > > student_id_FK
  > > phone_num_id_FK
  > > address_id_FK
  > >
  > 

  I think this table works well because most phone numbers are linked with an address. If
the student has two addresses (a home address and a school address) and 4 phone numbers
(two home phone numbers, a school phone, and a cell phone), there would need to be 4
records added to this table. The data would look something like this: 

  student_id, address_id, phone_num_id 
  ------------------------------------ 
  4,2,15 
  4,2,16 
  4,13,22 
  4,41,89 


  > 
  > If an "address" isn't any address, why doesn't it relate to a student?

  Odds are, if an address is not related to at least one student, it wouldn't exist in the
data. However, imagine you have been asked to build a "student finder" database for a
university. It should be practical to pre-load your database with all of the addresses of
the on-campus housing (all known student addresses). In that case you could have several
dozen "address" records in your database before adding any student records at all. The
relevance of the address records is not apparent if you just look only at the structure.
Rather it comes from the choice of the data you populate the tables with. 

  Koon Yue Lam:  To repeat what others have said, the query will correctly return
repetitive information for your student fields if there is more than one address or phone
number or some combination of either per student. Data retrieval tools are generally not
intended to present hierarchical information in a hierarchical manner. That is generally
accomplished with data analysis tools or data presentation tools or user-written code. 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 



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