Bill: If you can handle a trainee's $0.02:
I, too, wrestle with the question of 'too relational'. But you've hit on the sticking point between Excel, flat file and relational databases. Using your example, a 'family' has one or many members of interest to you. Each member may have one or many addresses of interest to you. Each member may have one or many phone numbers of interest to you. If one attempts to incorporate all of this into, say, a 'Contacts' table - such as you might have in a 'perfectly' sortable Excel spreadsheet - the amount of data duplication completely frustrates the goal of the relational model, which is, at bottom: 'enter each piece of data once only'. In this example, the number of rows (and the implicit data duplication) is calculated as: [family_1] x [member_1] x [member_1_addresses] x [member_1_phonenumbers] [family_2] x [member_2] x [member_2_addresses] x [member_2_phonenumbers] plus ... [family_n] x [member_n] x [member_n_addresses] x [member_n_phonenumbers] ... and if each member can belong to more than one family (like I, my wife, and my children all do), you're hosed. Or forced to make arbitrary determinations, in which case ... you're hosed. Sorry, but my conclusion is that once you 'go relational', you 'go big' or 'not at all'. The relational schema ends up looking like a spider web, BUT! The saving grace is that you then design your entry forms to take full advantage of normalization; which makes it easy to design powerful Views (which, if you think about it, are 'relational spreadsheets') from which you can generate whatever output you like without fear of funky outdated data lurking in the shadows. As an anti-climactic aside, one other trick is to maintain a binary 'flag' column for 'disable_date' in each of your data tables, so that you can 'turn off' the non-current cell phone assignment, while tracking the current assignment. I'm doing this with sequences of property title-holders, where I cannot allow former property owners to just disappear. Heck, terabytes are cheap these days. Hope this helps. Bruce Chitiea SafeSectors, Inc. > -------- Original Message -------- > Subject: [RBASE-L] - Too relational? > From: William Stacy <[email protected]> > Date: Tue, February 21, 2012 5:04 pm > To: [email protected] (RBASE-L Mailing List) > > > 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
