Re: HELP needed on design

I was going to say "Hire somebody who has the expertise to do this and not expect to get this scope of expertise given away", but Daniel Morgan beat me to it.

Helping someone out of a jam is one thing, but expecting someone to do a project for free is an insult.

RSH. "Van Messner" <vmessner_at_bestweb.net> wrote in message news:ub1nf9cqo98e5b_at_corp.supernews.com...
> Hello:
>
> I'm looking for the best physical model for an Oracle contacts database
> worldwide. I have a working model but I'd like to greatly improve it.
I'm
> trying to satisfy at least the requirements below, and Id like to see what
> any of you suggest right down to the table and column level. If, with
your
> help I can come up with a good model, I'd be happy to publish it back to
the
> newsgroup for everyone to use.
>
> A contact can be a person or a organization. Perhaps you'd use a contact
> table which stored the contact ID and not a whole lot else. The contacts
> table would provide a foreign key to a persons table and to a
organizations
> table. Also to any intersection tables such as addresses. That way you
> wouldn't have to have separate intersection tables for persons and for
> organizations to everything else. (Instead of an intersection between
> persons and addresses and another between organizations and addresses,
you'd
> just have one between contacts and addresses).
>
> It would be great to have an alternate key for persons other than contact
ID
> (to prevent you from entering the same person more than once with
different
> contact IDs). But what could it be? It can't be anything like social
> security number which is sometimes not legal to request in this country,
and
> which doesn't even exist in other countries. If you use honorific (Mr.)
> first name, middle initial, last name, suffix (Jr.) and title then you'd
> have to have all that information available when you entered data -
> something that's not always the case. I don't like email addresses or
> phones cause they change so often.
>
> Both persons and organizations can be hierarchical so we need to allow at
> least one parent. Is one parent actually enough? Probably not - so
what's
> the best model that provides a lot of flexibility?
>
> For addresses, geography can be a problem. In this country we often have
a
> hierarchy that looks like city, county, state, nation (USA). But
Louisiana
> has parishes not counties and Alaska has boroughs. We'll need to store
> labels for each level of the hierarchy. And New York City is composed of
> five counties (Kings, Queens, etc.) while every other city in New York is
> part of one county. So the hierarchy has to be flexible. And other
> countries have their own structures and unusual situations. Also address
> mailing formats vary from country. Some put the "zip" code before the
> country, some after etc. And how do we link informal geographic
information
> (voting districts, police precincts, neighborhoods) to more formal
> addresses?
>
> We need to store abbreviations wherever useful. The United Nations
provides
> standard abbreviations for nations and we have standard state and province
> abbreviations for Canada and the US. Other sources for other
abbreviations?
>
> We probably need to allow for historical references. When the Union of
> Soviet Socialist Republics became Russia and other nations what would have
> been the best way to handle the breakup and to know what were valid
> addresses ten years ago and to convert them to current addresses?
>
> For phones there is the problem of different structures in every country.
> If John Jones has a particular phone number in Newark, New Jersey, what
you
> dial to reach John depends on where you are in the world. What's the best
> way to model this? How do we isolate and store parts of phone numbers
(area
> codes?) for marketing purposes?
>
> Finally, we need to model the relationships between persons and
> organizations and other persons and organizations. Relationships can be
> many and varied and change over time. How can the model allow for this?
> John Jones might have been a contractor for ABC Corp, then an employee,
then
> gone off and started a small hardware firm which is now a supplier for
ABC.
>
> We've all modeled many of these pieces to one extent or another, so I
thank
> you for reading this far and for any help.
>
>
>
>
Received on Sun Apr 07 2002 - 22:27:45 CDT