Re: Table design - reducing number of entities

"Daniel J Watkins" <danielwatkinslearn_at_hotmail.com> wrote in message news:438d5f10$1_1_at_glkas0286.greenlnk.net...
> 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 Received on Wed Nov 30 2005 - 10:27:13 CET