Re: in need of guidance ...

Walter,

You have quite a problem here.

The data schema you describe likely involves two tables with 
"many-to-many" relationship. That's a bit tough to handle by itself. 
What I have set up for some systems (not all systems will require this) 
is three tables.

The first table holds data about persons. This could include birthdate, 
ssn, driver's license, etc--things that a person will have only one 
of--and an auto-increment primary key as an id.

The second table contains data about locations--the address for the most 
part--and an auto-increment primary key as an id.

The third table has only two columns. One column points to the person 
and one column points to the address. That resolves the many-to-many 
relationship.

Then there is the issue of the address itself. I'm also not sure why 
mail stops would be handled differently, but I know I have had trouble 
handling duplicate-checkers on addresses with mail stops. This depends 
on how your other tables store data that you will use to lookup county, 
state, time zone, etc.

In order to lookup time zone, you'll need more data than the tables you 
describe. If you want to validate zip codes, you'll need even more data. 
Most times, the street address will include a number, a direction, and a 
street name. You will either need to save this data discretely or you 
need to be able to parse a street address into these components. Neither 
is as easy as it would appear. The street number is generally first, but 
the direction (N, S, E, W, SE, SW) can appear before or after the street 
name. And then there are PO Boxes. You need to be able to identify them 
and differentiate between private and post office boxes.

Parsing all of this data from a single address line can get to be very 
complicated. Entering the data into multiple columns can become a chore. 
You'll need to find a balance point.

And, without knowing how the data is available for you to lookup the 
information you describe, you really don't have enough info to begin 
design. The zip code alone won't give you accurate county and/or state 
data all of the time--zip codes cross boundaries.

As I said at the top, you have quite a problem here, devising a system 
that will be all things to all people. It's not impossible, but probably 
beyond anything I would want or be able to tackle.

Good luck.

Randy Clamons
Systems Programming
[email protected]


Michael McBride wrote:
> Walter:
> 
> How are mail stops different from, say, a box number.
> 
> As far as I can tell, 
> 
> 	123 Penguin Place, MS87 
> 
> shouldn't be any different from 
> 
> 	123 Penguin Place, Apt 87.
> 
> Mike
> 
> -----Original Message-----
> From: phpWalter [mailto:[email protected]] 
> Sent: Wednesday, October 11, 2006 6:43 PM
> To: [email protected]
> Subject: RE: in need of guidance ...
> 
> 
> On Wed, October 11, 2006 4:47 pm, Michael McBride wrote:
>> Walter:
>>
>>
>> Since you're using an OpenSource package, you should have detailed
>> information on their schema for user/contact data.
> 
> I have that.
> 
> 
>> The first thing you need to do is figure out what, if anything, is wrong
>> with, or missing from, the existing schema.  Then you can start figuring
>> out what changes and/or additions you need to make to this schema so that
>> it meets your requirements.
> 
> It's not so much "my" needs.
> 
> The exsiting schema, and most user based system for that matter, don't
> handle:
>  * multiple people at the same company address
>  * single person with multiple adresses
>  * no one handles "Mail Stops" properly
> 
> My "need" to revamp ythis has come from a few years of working wit various
> OS apps and trying to integrate them, and requests for others in help
> integrating packages.
> 
> With this in mind, I thought I'd re-design (with a lot of help from those
> who know more than I do) how apps handle users, contacts and companies and
> their personal data.
> 
> Thus my plea for assistance.
> 
> I have a data collection of:
>  * all the counties and their codes
>  * all the US states and their codes
>  * all state counties and their codes
>  * all major cites (and some with codes)
>  * timezone details
> 
> I want to have this as a base set of data that all apps can utilize in a
> common format.
> 
> Example: a user enters an address, the app know knows the proper county
> and state codes (assuming US) and timezone and DST data automagically.
> 
> My concept is: is contact data is stored in central DB/table, than any OS
> app can utilize it, with a set of contact APIs. This also allows other OS
> apps to integrate easier to a companies contact data.
> 
> I "user" then becomes just a reference to a contact, complete with contact
> data and company information.
> 
> Does this help clearify what I'm trying to do?
> 
> Thx
> 
> walter
> 
> 
> 
> 

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]