I have a photo studio application where my master table have the
contract info -date,description... together with a address table, party
address table (that look at the address table) with a primary email
address, a separate contact telephone table, contact email and all the
data tables - this allows for multiple items to be related to the
contract - one to many
J
On 2/23/2012 10:22 AM, William Stacy wrote:
I think people can not only have several postal addresses, but they
can have several telephone #s, multiple work lines, faxes, etc. even
multiple e-mails are popular. But you're right about the shape
thing. I'm thinking that the street name and the city,state zip line
probably belong in their own tables as lookups. I think Folsom CA
95630 should only exist once in my entire database, not the thousands
of times it now does. But that brings me back to my title question of
this thread...
On Thu, Feb 23, 2012 at 4:38 AM, Bill Downall
<[email protected]
<mailto:[email protected]>> wrote:
William,
This is a design dilemma. Addresses are definitely a separate
table, because a person can have multiple addresses, and because
the "shape" of the data is different, (street address, city,
state, postal code, country). But do the phones and emails and
twitter accounts link back to the people, or to the addresses? I
tend to go with linking to the people, with phone types broken
down in "home phone" "mobile phone" "work phone", etc.
Bill
On Thu, Feb 23, 2012 at 7:25 AM, William Stacy
<[email protected] <mailto:[email protected]>> wrote:
Now this is interesting. Do you include Postal contact
types the same way, in the same table? Are these 2 columns
part of a personal demographic table, or a separate
table. If the latter, how do you link them up with the
personal table? TIA
On Wed, Feb 22, 2012 at 5:59 PM, Bill Downall
<[email protected]
<mailto:[email protected]>> wrote:
I cannot see the future as well as you, Mike. But my more
recent designs do not have any columns with the letters
p-h-o-n-e in a column name. There is a column for
ContactType, and another for ContactValue. I could someday
add a new contact type of ipv6, in addition to existing
types of email, mobile, work, google voice, twitterID,
etc. No schema change needed.
Bill
On Feb 22, 2012 5:46 PM, "Mike Byerley"
<[email protected] <mailto:[email protected]>> wrote:
I started using nnn.nnn.nnnn for phone numbers
anticipating at some time sub
ipv6, phones will just be IP numbers. Just a guess
though.
----- Original Message -----
From: "Bill Downall" <[email protected]
<mailto:[email protected]>>
To: "RBASE-L Mailing List" <[email protected]
<mailto:[email protected]>>
Sent: Wednesday, February 22, 2012 11:26 AM
Subject: [RBASE-L] - RE: Too relational?
It's nice to see Professor Wills here! You know a
topic like this would get
him going.
Bill, in my mind, a basic reason to normalize fully is
to create a database
that is least likely to need either schema changes or
awkward
exception-handling down the road.
If you do not normalize, and you provide room for 3
phone numbers, some day
you will have to put the fourth phone number in the
comments, or change the
schema to allow for 4 phone numbers.
Schema changes are expensive, because all forms and
reports and procedures
and eeps and views and rules and triggers and
applications that relate to
that data may have to be changed, too, and cannot be
done by users through
"settings", but have to be done by programmers.
Putting the data in the "wrong" place like the
comments means people won't
find that data with a normal search or query.
There are other good reasons to normalize, like not
"wasting" columns that
are usually blank, and not having to search three or
five columns instead
of one (For example, to determine what customer might
have sent us an
incomplete or garbled fax message or credit card
transaction where all we
know is that their address is "345 Main Street"). But
avoiding future
expensive schema changes is the main one.
Bill
On Wed, Feb 22, 2012 at 11:02 AM, Wills, Steve
<[email protected] <mailto:[email protected]>> wrote:
> “Too relational” is a state that is rarely achieved,
IMHO. I think your
> issue/question often and I like the direction of
your thinking. I guess
> that thinking about such makes me a little “twisted”
to some. I also own
> my own barcode-scanner - well enough about my
predilections!****
>
>
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
--
*J. Blaustein*
*J Blaustein Associates, Inc.*
* 12 Herrick Drive*
* Lawrence, NY 11559*
*516-371-3445 FAX 516-345-8009*
