Re: HELP needed on design

Besides, there are people out there that can't afford to work for $3 / hr that need work.

Like me.

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:28:53 CDT