List:General Discussion« Previous MessageNext Message »
From:SGreen Date:May 24 2005 7:16pm
Subject:Re: DB design question
View as plain text  
"Martijn Tonies" <m.tonies@stripped> wrote on 05/24/2005 02:32:05 PM:

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

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

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