Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP needed on design

Re: HELP needed on design

From: Daniel A. Morgan <damorgan_at_exesolutions.com>
Date: Sun, 07 Apr 2002 19:16:10 +0100
Message-ID: <3CB08CEA.91F9C08E@exesolutions.com>


The kind of thing you are asking for strikes me in two separate and distinct fashions. They are:

  1. I charge a rather substantial hourly rate to do this and you want it for free. If I am going to do your work for you I want to be paid for it.
  2. I would be violating professional ethics handing over to you work done for one of my clients.

Others may feel differently and help you ... but I doubt it.

Daniel Morgan

Van Messner wrote:

> 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 - 13:16:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US