Newsgroup: comp.databases.theory

I came across:

   Subject: Entity Overlap and Relationships

The first post in the thread was:

   From: GB

    1  1. What is the "industry best practice" in the following
    2  situation:
    3
    4  Say there are 3 types of entities {Entity1, Entity2,
    5  Entity3}
    6
    7  Each Entity type has unique attributes and similar ones.
    8
    9  For the attributes that are unique to each Entity it is
   10  easy (either they are in the table with the Entity or in
   11  the case of a one-to-many relationship there is a table
   12  related only to that Entity).
   13
   14  In the case where there are similar attributes (i.e. each
   15  Entity has multiple addresses - all of the same form {
   16  addressline1, addressline2, city, state, zip, etc })
   17  what is the proper way to handle this?  I was thinking
   18  of creating the following tables for each entity:
   19  Entity1Address, Entity1Phone, Entity2Address, Entity2Phone,
   20  Entity3Address, Entity3Phone with the Address and Phone
   21  tables all the same but related only to the relevant Entity
   22  table but something rubs me the wrong way here...
   23
   24  2.  What is the best way to handle people-people
   25  relationships (from a schema perspective - I can see the
   26  sarcastic remarks now)?  Has anyone solved this problem of
   27  how to store {father, son, uncle, friend, etc.}?  Did you
   28  have to set up a trigger to update the table to include the
   29  son relationship entry if the father relationship was added
   30  for instance?  Or would you just store the "parent" and
   31  "child" title for the relationship, in this case "father"
   32  and "son" then the related unique ID's for the people with
   33  the relationship?

"Oh, boy," I thought, "this is going to be entertaining."

Lines 4 and 5 set us up for a "general" situation.  Line 7
opened the gates.  Line 9 said this would be easy, except...

Lines 11 and 12 made me flinch.

"Uh, oh.  Here's that 'Entity relationship' thing, again, this
time masquerading as "...a table related only to that Entity."
(I've had to train myself from making the "I know what was
meant despite what was written' mistake.  Again.)

Without wasting time, the poster launched into a pseudo-design
and started slinging attributes and tables and Entities with
meaningful names about while revealing his thought processes
to the world.

Line 24 seems to pose a general question to the general
situation presented in the first two dozen lines.  After reading
the follow-on question starting on line 26, I thought, "Would
the poster be happy with a simple 'Yes?'  Probably not."

The soon-forthcoming presumption that the evasive general
solution might involve a "trigger" which maintained a
"relationship entry" confirmed what I suspected may have
caused the poster to turn to the newsgroups with such a trivial
problem: "Too many tools, not enough knowledge."

The first reply was spot-on advice.   And line 3 contains a bit
of prophecy that, once you watch this newsgroup for a while,
really isn't all that prescient.

   From: BB

    1  With all due respect, the answers to your questions
    2  will depend on the myriad requirements you have not
    3  mentioned. Anyone who pretends to have answers is a crank.

The OP politely thanked BB for his response, ignored it,
and pressed on with more magic dust: GUIDs!

     *sotto voce*  Do you know how many problems GUIDs have
     solved?  Here's a clue: the value's less than one.

   From: GB

    1  Thanks for the response, I really appreciate it even though
    2  I still might need to flesh out my problem a bit more.
    3  I guess my rephrased question is this: if I use a GUID as
    4  the ID for each Entity regardless of EntityType, should
    5  I have one table like EntityAddress that has an EntityID
    6  (GUID) column which relates to all three Entity Tables?
    7  Thus all Three Entity Tables would use the same address
    8  table?

Another optimistic contributor tried to get the original poster
back on track.  Without sounding dogmatic, and quietly restating
the fact that too many requirements are still unstated, JH
suggested a strategy for discovering a solution.

   From: JH

    1  You first of course need to establish whether that
    2  actually correctly models the situation. For example, is
    3  there really such a concept of *the* address of an entity
    4  (or *the* set of addresses) or could this depend upon
    5  the context (working address, billing address, shipping
    6  address, private address, et cetera).  After you've
    7  established this you then have to think about what is
    8  the more efficient option. What are the typical queries
    9  that will be asked. What integrity constraints can the
   10  DBMS maintain and do you want them maintained? All these
   11  things might might play a role in deciding what is the
   12  best option, and I probably forgot a few.

Words like "first" and "after" have well-known meanings.

The poster didn't get it.  He tries to convince us -- and,
perhaps, himself, that he has but, as we shall soon see, the
more he noodles the question the more obvious it is that he
has already flung himself into an abyss.

   From: GB

    1  Thanks Jan!  I think I understand in that the design should
    2  mimic the *carefully* thought out business requirements.
    3  In this case, each Entity type will likely have multiple
    4  addresses but the more I noodle on it I am thinking of
    5  doing what you rec'd in terms of using a fixed set of
    6  addresses for each entity type except for people.  I will
    7  likely run into the situation that the Entity Type that
    8  tracks people will have more than a "forecastable" number
    9  of addresses since most of the firm's clients (high net
   10  worth individuals) seem to have a multitute of addresses
   11  (winter home, summer home, beach home, hawaii home -
   12  nice problem to have I suppose).  As for the Business type
   13  entities, they will likely have less but more standardized
   14  addresses (sales office, ship to, invoice to, etc.).
   15  The third type is kinda like a VAR and has it own set
   16  of addressing issues.  I sincerely appreciate your post
   17  and it has kept me thinking.  I just hate getting further
   18  down the road and having to change something after I have
   19  migrated data over!

The designer has noodled some more requirements into the mix:
"multiple addresses" and a "fixed set of addresses" and
"more than a 'forecastable' number of addresses" indicate a
design-in-process that will soon need a capability-enhancing
retrofit.
And, lookity here!  On lines 6 and 12 we learn that Thing One
and Thing Two are people types and Business types.  On line 15:
Thing Three is a kinda type which "has it own set of addressing
issues."

   not so *sotto voce*  If "addressing issues" don't distinguish
   a "type" then I don't know what would!  What is a type,
   anyway?

My turn at a prediction: this project isn't going to get very
far "down the road [before the designer will have] to change
something after [he has] migrated the data over."  Too, I'm
sure that the designer believes the impact of such a retrofit
will be mitigated because, it seems, work is underway using a
design that will have to be enhanced to accommodate those other
"addressing issues."

Speaking of which: BB's prophecy has been fulfilled!  Help is
on the way!

   From: DW

    1  I'll give this first one a shot.  If I am understanding
    2  your question, you would likely want an Address table
    3  with a generated key.  Then each of your Entity1-3 tables
    4  would have an attribute that is a foreign key to the
    5  Address table.
    6
    7  I wouldn't typically do the same thing with phones, even if
    8  storing PhoneType PhoneNumber pairs, as there is an ongoing
    9  cost for such designs.  But if you need to store multiple
   10  phones for an Entity (and are saddled with a SQL-DBMS),
   11  then that would be OK.  You might instead have an attribute
   12  for BusinessPhone, HomePhone, and MobilePhone for each
   13  Entity.  You are out of luck if you have two business
   14  phone numbers then, but this is often sufficient and I
   15  suppose you could add a catch-all OtherPhone.  You will
   16  obviously need to know your precise requirements.

Wow!  Foreign keys.  How did we miss that?  And new entities.
I think.  Phones?  Of various types?  And typed attributes
for Entities?  Or are those attributes to hold values of
certain types?  Except for the "catch-all Other?"

Hmm.  I'm having trouble keeping up: are those other types or
other values?  And where, exactly, does this design run out
of the luck referred to on line 13?  When the number of values
of type BusinessPhone exceeds one?

I'm going to keep an eye on this discussion to see how my
predictions work out...

-----

What's the point of this oblique rant?

While BB's blunt replies to and comments on other posts
jolted me, I've come to feel that he has faced and embraced a
worthwhile truth: being polite is sometimes a greater barrier
to communication than being rude.

This is made more difficult by the fact that we have been trained to be
ashamed of our own ignorance, while being light hearted about our own
stupidity.  Witness the proliferation of books "for Dummies"  (a reference
for the rest of us).

I'm one of us, but I'm not one of the rest of us (in that sense).  I hope I
share that with most of the readers in this group.

What I would wish from someone like BB is that he could, by now, offer links
to websites that develop the ideas in proper detail,  and offer a positive
alternative.  I refuse to believe that no such websites exist,  or that BB
doesn't know of any of them.

However, those links would not substitute for a proper education on the
subject. For that, at a minimum, one would have to read a number of books.

For those whose goal is to use database management systems, I suggest
one can make a very good start at the book list on Fabian's site:
http://www.dbdebunk.com/books.html

Date's _Introduction ..._ is comprehensive. Fabian's book is good for
those who already know a bit of SQL or some of the RM. _The SQL
Standard_ will be less useful to most people.

All I did was warn someone against listening to cranks who pretend to
have solutions without knowing requirements. Jan, at least, provided
pointers on how to flesh out the requirements.

In this situation, there is a high probability he will become a
consultant where he can earn a lucrative fee for repeating the design
and then move on before the client has to deal with the headache of
implementation.

There is of course a much, much larger chance that after the database is
populated with data and the applications are all written and rolled out
that the person will discover that some important users will actually
want to see some reports based on the data. At this time, there is the
very high chance of complaints due either to incorrect results, delays
and difficulties writing correct reports, or performance issues.

http://members.aol.com/intwg/antiprocess.htm

I have already shown that my car can run a never before tested track in
10 seconds. Now you want me to get in your car (you being Richard Petty
at his pinnicale, having demonstrated to be the fastest car driver in
the world) and show you it can't? Now suppose, I take your car for a
lap and it either comes in at 12 seconds or worse yet never makes it
around the track, would you believe the results? Or would you want to
jump in the car and prove it otherwise by yourself?

If you say that you would believe my results, then here is my run:
CREATE TABLE T_Thing (ID, Name);
INSERT INTO T_Thing (1, judge);
It didn't even make it around the track, so I can't report a time for
comparison.

Searching got me nothing better than the above.

JOG, you are a FUCKING MORON! You haven't a CLUE. You have me waste 35
FRICKIN minutes reading some BULLSHIT about skeptics and paranormalist
debating things that only exist in they CAVERNOUS SKULLs that neither
can verify? You mean your less than PEA-SIZED BRAIN can't distinguish
between the those type of debates and the ones here?

     Paraphrasing Sam Clemens, you have the words but not the tune.

Sincerely,

Sincerely,