database design question

[gkelly]

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?

gkelly

[ak_tire...@yahoo.com]

>> Or,
would you have had the tables Faculty and Student store
the last, first, addr and other fields?

... <<

What if Professor of Mathematics also takes cources in Digital
Photography? Are you going to store her phone number in 2 rows, both in
Faculty and Student tables? What if a post doc student also has a part
time job teaching?

Depending on the platform, you might find materialized query tables aka
materialized views, and / or index covering to be good alternatives to
denormalization. I'm speaking about DB2 and Oracle

[Impulsive]

My question is - why do you need to denormalize this?
Are there heavy reports being run on this data that don't execute fast
enough?
What kind of reports?
What other info is in faculty and student tables besides foreign keys?

[B Blink]

On Thu, 6 Jan 2005 12:30:31 -0700, "gkelly" <gke...@xmission.com>
wrote:

In general when performance becomes an issue.

>
>gkelly
>

[gkelly]

No, I do not 'need' to denormalize this. and I don't want to change it just
for the sake of change.

What would you do when you want to close out one year and start a new year.
If you had standalone student and faculty tables then
you could archive them easily. Have a school semester and year attached to
them.

However, as you go go from one year to the next infomation about a student
or faculty may change. Like their addr and phone for example.
The database model now is not very good because it doesnt maintain a
history. If Student A was in school last year as well but lived somewhere
else
would you have 2 contact records? 2 student records? or do you have just
one of each and have a change log. which is best?

gkelly

"Impulsive" <sige...@gmail.com> wrote in message
news:1105042366.6...@c13g2000cwb.googlegroups.com...

[gkelly]

The student table besides having a foreign key to the contact would have
also the following:

medical comments
current grade
foreign key to account which is just another foreign key to contact table
for the parent/billing record.
...

for the faculty table, additional fields would include:

hire info
login/password for electronic timesheet login
...

"Impulsive" <sige...@gmail.com> wrote in message
news:1105042366.6...@c13g2000cwb.googlegroups.com...

[DA Morgan]

gkelly wrote:

One table name PERSON. Anything else is a denormalized nightmare.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)

----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---

[Impulsive]

Do you need to track all and every change to the tables? Then I would
go with logs. If no - are your needs restricted to tracking address
changes?

[gkelly]

There are no real requirements for this database. They are simply happy to
have whatever is provided.
It would be easy to keep a log of changes; however, I am unsure of a more
fundemental issue.

I know this next statement is riddled with problems but, just for fun,
consider this:

You track changes yes, but at the end of each day or week or whatever you
compare the current record with the last snapshot of the record
which would simple be an xml based representation of the record. If the
current record differs from the snapshot then you create a new snapshot.

you could have a table like:

SnapshotXML
id int
dts datetime
TableName ...
TableID int
xml clob -- this field would hold the xml snapshot of the
record

I know this leaves my initial question concerning normalization somewhat,
but is still related.

"Impulsive" <sige...@gmail.com> wrote in message

news:1105052121.8...@f14g2000cwb.googlegroups.com...

[Eric Bohlman]

"gkelly" <gke...@xmission.com> wrote in
news:crk3ko$im3$1...@news.xmission.com:

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 object-
oriented 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 work-
related mail at another?). Otherwise, it looks like an attempt to micro-
optimize space usage.

[Neo]

> 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 - (probably should have called it Entity)
> Faculty - holds info about specific faculty member - fk to contact
> Student - holds info about specific student - fk to contact

Script below models sample data using experimental db. Each thing
(closest to entity in your description) can have 0 to many
classification. Each thing can have 0 to many attributes. Additional
classifications/attributes are created as needed.

// Create entries in directory to classify things.
CREATE *faculty.item ~in = dir;
CREATE *student.item ~in = dir;
CREATE *person.item ~in =dir;
CREATE *business.item ~in = dir;
CREATE *book supplier.item ~in = dir;

// Create address related entries.
CREATE *address.item ~in = dir;
CREATE *street.item ~in = dir;
CREATE *city.item ~in = dir;
CREATE *state.item ~in = dir;
CREATE *zip.item ~in = dir;

// Create Bill who is a teacher.
CREATE *bill.cls = person;
CREATE bill.cls = faculty;
CREATE bill.address = (CREATE *.cls = address
& it.street = +100 main st
& it.city = +chicago
& it.state= +illinois
& it.zip = +60600);

// Create Jill who is a student.
// Create email attribute on the fly.
CREATE *jill.cls = person;
CREATE jill.cls = student;
CREATE jill.address = (CREATE *.cls = address
& it.street = +200 birch rd
& it.city = +tulsa
& it.state= +oklahoma
& it.zip = +67670);
CREATE jill.(CREATE *email.item ~in = dir) = +"ji...@hot.com";

// Create Dill who is a teacher and student.
// Create apt attribute on the fly.
// Create age attribute on the fly.
CREATE *dill.cls = person;
CREATE dill.cls = faculty;
CREATE dill.cls = student;
CREATE dill.address = (CREATE *.cls = address
& it.street = +200 birch rd
& it.(CREATE *apt.item ~in = dir) = +D8
& it.city = +muncie
& it.state= +indiana
& it.zip = +49001-3456);
CREATE dill.(CREATE *age.item ~in = dir) = +18;

// Create Waldens, a book supplier.
// Create po box attribute on the fly.
CREATE *waldens.cls = business;
CREATE waldens.cls = book supplier;
CREATE waldens.address = (CREATE *.cls = address
& it.(CREATE *po box.item ~in = dir) = +456
& it.zip = +43432);

// Create food caterer Mac Donalds.
// Create caterer classification on the fly.
CREATE *mac donalds.cls = business
& it.cls = (CREATE *caterer.item ~in = dir)
& it.address = (CREATE *.cls = address
& it.po box = +309
& it.zip = +43432)
& it.email = +"ron...@macdonalds.com";

// Find a caterer with zip 43432.
// Finds Mac Dondalds.
SELECT %.cls=caterer & %.address=(%.zip=43432);