When is enough, enough?

[Anon]

I am designing an alumni mailing list database is 20,000 or so entries. I have received some conflicting advice about how much to normalize my DB. I have two types of "objects", people and institutions. People could be alumni, employees, affiliate, etc. Institutions could be schools, businesses, etc. I was advised to condense all of these possibilities into the two categories above. Now I am at a cross-roads where I must decide if I should be clever and have only one table holding addresses for both categories (people and institutions - 3NF of course) or if I should have an address table for 'people' and an address table for 'institutions'. This decision will apply to other tables besides 'address' such as e-mail, phone, etc.

While both implementations will work, I feel like one must be much less efficient than the other. Any advice would be much appreciated.

Dave

[[deleted]]

Hmm.. normally you'd only split up tables if it serves a purpose, like when it reduces data duplication or it enhances security.

In your case I see no reason to split the adres table up.

[Anon]

Hmm. Well, the point of normalization is to avoid having duplicate data. It isn't really to get caught up in insanely complex database tricks or storage formats. So free yourself from that limitation, you're not beholden to it.

Having said that, I would make details such as email, phone, address, and so on, tied to each person/institution/other. I would not normalize that data. Here's why: you have 2 people and a school that are at the same address. Being normalized, you would assign them all the same address id (say, "255"). Then you would have an address table, and at id 255 you would list the address just once. That's brilliant, saves space, and allows for a single update to the address to be used by all 3. The problem is that you don't want all 3 to get the update. If people are roomates with a shared address, and one moves out and changes the address, you don't want the address to change for the person who stayed. You don't want a person who lives at a school's address to graduate, move back home, change his/her address, and end up inadvertently changing the school's address too.

Because of this, you pretty much need to avoid normalizing that data. If 2 people have the same address, it'll have to be in your database twice. Because eventually, 1 of those 2 people will move.