DJK(John) Robinson
djkr at msn.com
Sun May 16 19:11:51 CDT 2004
Yes, of course people are in a many-to-many relationship with addresses, too, for various reasons. I would be very wary of telling just some Mary Smith that whe was pregnant. However, a particular Mary Smith has presented herself recently for a pregnancy test, presumably, and has given her name & address / whatever to enable me to communicate the test results to her alone with certainty thereby identifying herself uniquely in this context. Context is important. In real life there is unlikely to be a problem here, so it is up to us to design data structures and systems which reflect real life when relevant, so that they are no more fragile, and have the opportunity to be much more robust. John > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of > Susan Harkins > Sent: 17 May 2004 00:25 > To: 'Access Developers discussion and problem solving' > Subject: RE: [AccessD] normalization question > > > Well, I did think of that -- but then how do you determine > which Mary Smith did what in your database? Through > relationships of course, but one false move and zip...... > you're telling Mary Smith at 101 First Street that she is > indeed pregnant... No, you're not pregnant, Mary Smith... > Well, yes you are > -- when actually Mary Smith at 101 First Street IS pregnant, > it's Mary Smith at 201 Main Street that is NOT pregnant -- > know what I mean? The potential for mistakes seems > horrendously large. One wrong relationship (pun intended) and > let the data fall where it may! :) Of course, that would be > true in any database -- but we tend to think of relating the > data and transactions to the person or business, not the > address or the hire date, or birth date, or whatever other > value we end up having to use -- because Mary Smith isn't a > duplicate, just her name is. :) > > Let's do it this way -- you have a table with just one Mary > Smith, but you have two addresses linked to Mary Smith. Does > Mary Smith have two addresses, or are there two Mary Smith's? > You can't possibly know and the difference might be > important. ON the other hand, you can easily see if NameID: > 101 Mary Smith has two addresses or if NameID: 101 Mary Smith > has one and > NameID: 306 Mary Smith has another. What if it's <gasp> both? > What if one Mary Smith has two addresses and one Mary Smith > has none or one? Or, what if there are 3 Mary Smiths... Could > I BE more obnoxious? :) > > In the end, it seems like there has to be another > distinguishing value, as Martin suggests or we have to accept > some redundancy. > > OK, let chaos ensue! ;) > > Susan H. > > By having a table of people, of course. There is a > one-to-many relationship between people and names. (Or more > accurately a many-to-many relationship - but let's not get > into that!) Each person with the name "John Smith" would be > represented by a record (in the people table) containing a > pointer to that name in the name table. > > If you had a table of eye colors, how would you deal with > real people with the same eye color? It's the same question > as yours, really. Eye color doesn't identify a person > uniquely: nor does a name (well, some might). > > Sorry to sound simplistic, but that's it, really. No cliff > to step off. There remains of course the issue of how to > identify people uniquely - but that, as they say, is another story ... > > John > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan > > Harkins > > Sent: 16 May 2004 21:43 > > To: 'Access Developers discussion and problem solving' > > Subject: [AccessD] normalization question > > > > > > If a table contains only names -- say first and last -- and > you relate > > those records to other tables -- how to you deal with real > people with > > the same name? I feel like I'm stepping off a cliff here... > > > > Susan H. > > -- > > _______________________________________________ > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > -- > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >