From: Martijn Tonies
Date: May 24 2005 7:35pm
Subject: Re: DB design question
List-Archive: http://lists.mysql.com/mysql/184517
Message-Id: <00b801c56097$b9873020$3802a8c0@martijnws>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00B5_01C560A8.7D041940"

------=_NextPart_000_00B5_01C560A8.7D041940
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

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

  Not necessarily random but yes, those would be addresses.=20

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

  Yes. Again, not necessarily random.=20

  >=20
  > >
  > > student_info
  > > -------------------
  > > student_id_FK
  > > phone_num_id_FK
  > > address_id_FK
  > >
  >=20

  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:=20

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


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

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

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine=20



------=_NextPart_000_00B5_01C560A8.7D041940--