All hail Bob!

    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.

If someone tells you that you're a rock-headed moron, you just might be. For some of those, the next obvious question is,
"Do I want to stay that way?"
Received on Fri Apr 21 2006 - 06:32:01 CEST