Re: Table design - reducing number of entities

"Daniel J Watkins" <danielwatkinslearn@xxxxxxxxxxx> wrote in message
news:438d5f10$1_1@xxxxxxxxxxxxxxxxxxxxxxxxx
> I need some advise on this subject...
>
> Previously in my ERD there have been separate entities for 'Home Address'
&
> 'Semester Address' which the 'Student' has foreign keys for. I was
thinking
> of just having an 'Address' entity which that 'Student' has two foreign
keys
> for - one for each address type. Is this ok to do? Will I lose any marks
for
> modelling like this? I suppose the correct way to do this is to use a many
> to many between 'Student' and 'Address' now and use a juntion entity,
rather
> than use two foreign keys?
>
> This is the same for the 'Supervisor' and 'Training Offficer' entities
which
> the student has - why not just use an 'Employee' entity but is it correct
to
> use a foreign key for supervisor and training officer which both reference
> employee - that removes the need to use a juntion entity!

Before offering my opinion, I am curious to know why practically everyone in
the real world seems to have an instinct to minimize the number entity types
in a logical design by forcing them to be ever more generic? Why does
everyone seem to think there is a need to minimize the number of tables in a
physical database design? I ask because the more generic you make them, the
more code you are going to have to design, write, test, document and
maintain to *impose* the business model at run time. It just makes no sense
to me. Coding it very slow and expensive. I bet half the code I have to
look at (pretty crappy code too BTW) is there just to make the generic
specific again. (End of rant.)

My rule of thumb in the real world (where I earn my miserable crusts) is to
look at all the constraints on the putative entities--not forgetting the
transition constraints. If two entities have the same constraints, they are
the same entity type and they belong in the same table. And crucially if
they *don't* have the same constraints they are not the same entity type and
they absolutely do NOT belong in the same table. My very strong intuition
is that "home address" and "semester address" have very different meanings
and hence different constraints. Likewise (perhaps even more so) for
"supervisor" and "training officer". However in both cases there might be a
significant number of attributes that the pairs of entity types have in
common, and one could (and I say should) use a super-entity to represent
*just* the common attributes that have the same constraints. And there I
think perhaps we meet in some kind of middle ground.

Roy


.