9 messages in com.mysql.lists.mysqlRe: DB design question
FromSent OnAttachments
Koon Yue Lam24 May 2005 10:33 
Bartis, Robert M (Bob)24 May 2005 10:52 
Berman, Mikhail24 May 2005 10:52 
Mike Johnson24 May 2005 11:02 
Mike Johnson24 May 2005 11:06 
Martijn Tonies24 May 2005 11:31 
Gordon24 May 2005 11:39 
SGr...@unimin.com24 May 2005 12:15 
Martijn Tonies24 May 2005 12:34 
Subject:Re: DB design question
From:SGr...@unimin.com (SGr@unimin.com)
Date:05/24/2005 12:15:55 PM
List:com.mysql.lists.mysql

"Martijn Tonies" <m.to@upscene.com> 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