Abnormal Normalization?

[Brian Kastel]

I took people's names out of my Employees and Contacts tables, and created a
Names table with the PK linked to a FK in each of the two original tables.
I did this because sometimes the employee could also be the contact, or
vice-versa. But I didn't stop there. I created four tables; one for name
prefixes (Mr., Ms., etc.), one for name suffixes (Esq., Ph.D., etc.), one
for forenames (for first and middle names), and one for surnames. So now I
have nothing in the Names table except for its own PK and the five FKs
linking with the simple tables (First and Middle names both being linked to
the Forenames table). I know this is decidedly 3NF, but does anyone think
that I am overdoing it?

The madness doesn't end there, either. Employees, Contacts, Job Sites, and
Clients can all share one address. Of course, I now have one Addresses
table linked to those four, but I found I had to assign each address a
"title" to allow for practical selection of the address from a list or
combo. Again, am I overdoing it? Laziness prompted me to create this
structure: since I would be the one doing the data entry, I simply didn't
want to enter data more than once, ever.

 

[tina]

well, if a person can fill multiple roles in the data you're storing, i can
see a reason to have a separate "person" table. and storing honorifics in a
table as opposed to using a value list in the RowSources of combo boxes, i
do that myself - for several reasons. you could apply the same reasons to
justify a separate supporting table (i hate to call them lookup tables) for
the suffixes.
but the first/middle and last name separations? yeah, i think you're going a
little table-crazy there. <g>
and the addresses bit? i'm not even going to touch that one! but i have to
admit the idea of an employee also being a contact (which i've always used
with the connotation of an entity *outside* my customer's organization), and
the idea of an employee having the same address as a client, or a job
site....all this makes me wonder what-in-the-heck kind of business this
database will be used in! <big g>

 

[Brian Kastel]

lol... Let me set your mind at ease... It is a construction company. Some
of the employees end up contracting the company itself to do remodeling or
repairs, in which case their address becomes all four addresses! I had to
separate jobs from clients because a client can contract for more than one
job, and I had to separate contacts from clients because many clients could
share one contact and many contacts could be assigned to one client. It even
does labor tracking per job and payroll calculations. I thought it was a
masterpiece of design when I was finished (HUGE <g>). If attaching binaries
were not rude, I would show you a picture of the relationships in this puppy
(all with ERI)...

I just wanted a reality check. Thanks. I'm really just an advanced
dilettante. I can write VB code in my sleep (I actually have a copy of
VB1 -- it's DOS-based, if you didn't know), and I love designing RDs in
Access because of that. Sometimes, I even get paid to do it, though not as
much as I would like (in either sense of the word). I came here the other
day to ask a question, got an answer in five minutes, and stuck around to
try to give back a little bit if I could.

Peace.


well, if a person can fill multiple roles in the data you're storing, i can
see a reason to have a separate "person" table. and storing honorifics in a
table as opposed to using a value list in the RowSources of combo boxes, i
do that myself - for several reasons. you could apply the same reasons to
justify a separate supporting table (i hate to call them lookup tables) for
the suffixes.
but the first/middle and last name separations? yeah, i think you're going a
little table-crazy there. <g>
and the addresses bit? i'm not even going to touch that one! but i have to
admit the idea of an employee also being a contact (which i've always used
with the connotation of an entity *outside* my customer's organization), and
the idea of an employee having the same address as a client, or a job
site....all this makes me wonder what-in-the-heck kind of business this
database will be used in! <big g>

 

[tina]

well, that all sounds normal and above-board - whew! <g>
and, welcome to the crowd. i've been coming here almost a year; i answer
some easy questions - and mostly read a lot and learn a whole bunch. anyone
who can write VB in their sleep is bound to be an asset to us all (well, so
long as you're not coding during nightmares!)