"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!
So, related to your message, let me offer as example, using anonymized data, my
own family ...
There are 4 of us (and 2 dogs, but they will remain "non-entities" for this
example). I will restrict this to some simple connections between "us", our
address, and our telephone numbers.
TABLES COLUMNS (PK's/FK's, Mostly) DATA
(Presented as CSV rows) AKA
-------------------- ------------------------------------------------
-----------------------------------------------
-------------------------------------------------
PERSON(S) Person_ID (SSN, TAXID, or whatever)
111-11-1111, Steve
Name
222-22-2222, Carolyn
333-33-3333, Pete
444-44-4444, Rebecca
ADDRESS(ES) Address_ID (an AUTOINCREMENT INT?)
000999, 123 ABC Street, Anyplace, TN, 381xx Some location, with mailing
attributes
Street_Address_String
City_Name
State_CD
ZipCD
HOUSEHOLD(S) Household_ID
111111, Wills A household named Wills
Household_Name (or Description ...)
HOUSEHOLD_PERSON(S) Household_ID
111111, 111-11-1111 Steve, of the Household Wills
Person_ID
111111, 222-22-2222
111111, 333-33-3333
111111, 444-44-4444
PHONE(S) Phone_ID (USA, only f/this example)
901-123-1111 Just some phone#'s, possibly
shared, possibly not
901-123-2222
901-123-3333
901-278-8888
901-448-4444
901-999-1111
PERSON_PHONE(S) Person_Phone_ID
901-123-1111, 111-11-1111, 2 Steve's Cell
Person_ID
901-448-4444, 111-11-1111, 1 Steve's Work
Phone_Type_ID
901-999-1111, 111-11-1111, 3 Steve's Home
901-123-2222, 222-22-2222, 2 Carolyn's Cell
901-278-8888, 222-22-2222, 3 Carolyn's Work
901-999-1111, 222-22-2222, 1 Carolyn's Home
901-123-3333, 333-33-3333, 2 Pete's Cell
901-999-1111, 333-33-3333, 1 Pete's Home
901-999-1111, 444-44-4444, 1 Rebecca's Home
--*** Pete is in HS, has cell# but
doesn't have work#. ***
--*** Rebecca is in 5th grade and
not in possession of a work# or cell#. ***
PHONE_TYPE(S) Phone_Type_ID 1,
Home How is this phone used when
INSERTed||UPDATEd in PERSON_PHONES?
Phone_Type_Name 2,
Cell Single Phone# could be used by
1:N persons in 1:N ways.
3,
Work
4, FAX
8,
Toll-Free
A little look at something like this could rapidly evolve into related
issues/questions, such as the following, among others:
Ø Decomposition of Address String into its discrete components (primary
address number, predirectional, street name, suffix, postdirectional, secondary
address indentifier, and secondary address). Reference something like :
o http://www.columbia.edu/~fdc/postal/
o http://pe.usps.gov/cpim/ftp/pubs/pub28/pub28.pdf (Section 231)
o GIS-Related Attributes, about which my knowledge is limited, but I think
Razzak, Bill_D, Et Al. have had some informative discussion-threads on the
topic.
Ø Can table PERSON become PARTY and, thereby be a superclass of PERSONs and
ORGANIZATIONS, including HOUSEHOLDs?
Ø Can table PHONE become ELECTRONIC_ADDRESS and ADDRESS become
PHYSICAL_ADDRESS or could each of those be put into some sort of super-class?
Ø Could the above be used to address the challenge of how many (mass-)mailings
should be made, as in PER-HOUSEHOLD or PER-PERSON?
All this came to be on my mind WAY BACK IN THE DAY when I first began using
RBase System V and began to learn about data-modeling/database design. At some
point I realized that a de-normalized table and its data could slide right by a
person simply because we are so accustomed to seeing something in "the way its
always been done" that we fail to reflect on it (at least within a
database/data-modeling context!).
This happened to me around the time cell phones became widely used in the US.
It struck me that almost every database in the world would have to have one or
more tables ALTERed, adding the column, `CELL_PHONE`. However, I already knew
that having to add a column because the external world seemed to have changed
usually meant that the data model was not sufficiently evolved. So, taking
another look, I realized that, ALONG WITH ALL THE NULL'S in tables with
`%PHONE%` columns, all the values looked to be about the same, logically
speaking, i.e. areacode-exchange-number, or "555-123-4765". That's when the
light went on! Such tables should have something like `PhoneID` as a column,
with how the phone is utilized as a different attribute/column. After that
came the realization that some phones are shared, some are not, some are
associated with PEOPLE, some with ORGANIZATIONS, and so on and so forth.
Of course, it naturally followed that there would be questions, AND CHALLENGES,
related to ADDRESSes, PARTY (people & organizations), PARTY,
PARTY_RELATIONSHIP, etc.
So, since I can only view myself as one who acknowledges the validity of the
issue/thinking, but not an expert with a solution, I will close with a
recommendation to look at one of the versions of the Data Model Resource Book:
http://silverston.wiley.com. Some of the TABLE/COLUMN names I used were lifted
directly from my copy of the book - man, my version is OLD! There are other
web resources, of course, some great, some not so great, but I do think this
one is pretty good because it has lots of reasonably-evolved models:
http://www.databaseanswers.org/data_models/index.htm. I can't recall if the
author(s) has a DBMS preference - as we all do for RBase - but the models can
be helpful. If anyone still has a copy of it, there was a time or two when
RBase sold/released some "application-packages"; I think one was during the
System V era and one later (2.x||3.x||4.x?). At least one of these came with
documentation which, if I recall, described the data models. If it did not, I
think they were viewable and/or editable. Regardless, my perception is that at
least some of those models were pretty good. However, it's been so long that I
can't say if any might apply to your question(s).
Well, maybe this helps, somehow. I hope you didn't fall asleep before you
reached the end of the message!
CU,
Steve in Memphis
From: [email protected] [mailto:[email protected]] On Behalf Of William Stacy
Sent: Tuesday, February 21, 2012 7:04 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Too relational?
In my efforts to "normalize" my database, I'm even finding the need to split
off postal addresses, telephone numbers, etc into separate tables. Presently
all addresses and phones etc reside in a person table and/or in a family table.
I haven't done this yet, but am thinking about it. In the end, my "family"
table may end up only having about 3 or 4 columns, which identify the person's
mom and dad for blood relative connections, and maybe a responsible person
(bill-to and family addressee) connections. Can't think of much else that is
needed there.
My reason is this: many people have alternate addresses, some more than 2 such
as office addresses, PO Boxes, military addresses, vacation homes, bill-to
addresses, girl-friend or sugar-daddy addresses and so on. Same thing is true
of phone numbers. Really, these are almost one-to-many items. In the reverse
fashion from what you might think. Really, a single street address can have
many people associated with it, and the address itself really doesn't change,
only the residents do. Same thing is true of phone numbers. My cell num is
unique, but after I give it up, someone else will eventually get it.
Anyone construct tables thusly?
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
