Re: clarification Re: 2 people with same address - sometimes - standard data model? [really a user interface question]

L Carl Pedersen (l.carl.pedersen_at_dartmouth.edu) wrote:
: i'm realizing now my original post was not clear. i was asking about data
: models when i should have been asking about user interfaces.
 

: if i'm storing the same address for several different people it's fairly
: obvious i need to have an address table separate from my person table. if
: i have only one address per person, i can store a sequence key to the
: address table in my person table. if i have more than one address per
: person, i'll need another table in between. the exact structure of the
: address table is not really what i'm worried about right now. i might
: decide to use the zip to determine city & state for example (there are
: pros and cons to this).
The way I would do this would be to have a table with client_info and an client_address table. The client table could use ssn as a primary key. The address table could use a concatenation of several fields to stop duplicates. Then use ssn is the client_address table to form a relationship. This would easily allow several addresses per client. You could concatenate the ssn into the primary key to allow different clients to have the same address. If the user were using sqlforms you could have a multi record block for the address(es). I probably wouldn't use ssn because of privacy concerns. The same methodology could be applied to any one to may relationship when you are designing an application. It does get get more difficult when you have a many to many relationship but there are ways to make thos work.

As far as determining city and state from the zip it sounds good in theory but from my own experience it is more trouble than it is worth. For example, if you wanted the name of every client in a particular city the query would have to work backwards from the table with the info about the cities. I would tend to de-normalize the data to improve processing speed by including a field for city and state. in the client_address table.
If processing speed is inconsequential to you, use a fully normalized design. It would improve the referential integrity of the application, and slightly decrease the storage requirements.. I gave up on the idea of determining the address from the zip when I was given about a hundred page of zip codes for Southern California. Also occasionally a client will get a zip code wrong using the relational model you could end up with the mail going to the wrong city. And what should a user do when a client leaves their zip code off of whatever the user is using to get the data from?

: my main question is, how should this look to the user doing address
: maintenance? it's going to be very common to have one address for one
: person. multiple people at an address is also going to be common.
: i'd sort of like to show a person's address on the person screen, even if
: it's not stored in the person table. i have plenty of room on my screen
: for this and i don't like the idea of forcing the user to know that that
: there's more than one table being updated. maybe i'm being wrong-headed
: here.
 

: i need a way to show when people live together on the screen and i need a
: way to change who lives with who. seems like i need to support a "move in
: with" and a "move out" operation and i need to make it easy to correct an
: address that has errors, regardless of how many people share it.
I would handle something like this at the application level with triggers in a form. This could be done by using a form based on the client_address table. Yould could use a subform listing the clients that live in a particular address with a multi record block.
: i might want to have my system automatically scan for duplicates when a
: new address is entered and ask the user whether they mean to have the new
: person living with an existing person or not. however, i want the user to
Again I would do this at the form level. A good example of a way to do this would be to use the phone number similar to the way Pizza Hut does. Then when the user starts entering a phone number, the field. (I use a field called phone) could have a list of values key with a bunch of logic built into a pl-sql procedure
: have control over this decision. it may be, for example, that two people
: have similar addresses that are longer than the space i have allowed to
: store it, or that we have incomplete information, and that the two
: addresses are "different" even though they look the same. (the post
: office can and does deal with this by noticing the name.)
 

: does anybody have a nice way to present this to the end user?
 

: i apologize for my original unclear posting. it resulted from unclear thinking.
Received on Wed Jan 18 1995 - 05:55:17 CET