Re: database design question

"gkelly" <gkelly_at_xmission.com> wrote in news:crk3ko$im3$1_at_news.xmission.com:

> I have a database for a school that has been in use now for a couple
> of years and it is working well.
>
> There are numerous tables obviously but consider these:
> Contact - holds all contacts - students, faculty, or any other type
> of contact - (probably should have called it Entity)
> Faculty - holds info about specific faculty member - foreign key
> to contact
> Student - holds info about specific student - foreign key to
> contact -----------
>
> Considering that the contact table has fields such as: last, first,
> mi, addr, city, state, zip, email ...
>
> Do you think it is a good idea to have a single table hold such info.
> Or, would you have had the tables Faculty and Student store
> the last, first, addr and other fields?
>
> At what point do you de-normalize for the sake of being more
> practical?

I don't see how having separate tables for fundamentally different types of contacts could be considered denormalization. In fact, your current scheme of having a bunch of 1-1 relationships seems to reflect objectoriented  thinking rather than relational thinking; it looks like your "faculty" and "student" tables and their 1-1 relations are attempts to "subclass" your "entities."

IMHO, it would make more sense to have separate tables for each type of "entity," each containing all and only the attributes that make sense for a faculty member, student, etc. For one thing, it would make referential integrity checking easier. As I see it, the only virtue of your current scheme is that if a faculty member happens to have a kid at the school, their contact information is stored in only one place. That's likely to represent only a few cases, and it's subject to change (what if the teacher gets divorced and the other parent gets custody of the kid? What if somebody wants to receive personal mail at one location and workrelated  mail at another?). Otherwise, it looks like an attempt to microoptimize  space usage. Received on Fri Jan 07 2005 - 05:02:55 CET