"Martijn Tonies" <[EMAIL PROTECTED]> 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
