"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
> 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
> 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
> Upscene Productions
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
Unimin Corporation - Spruce Pine