in need of guidance ...

I'm in the process of specing radical changes to an opensource CRM package
I've been working with for the past few years.

I need guidance in regards to a schema for the user/contact data (name,
address, etc).

If anyone can lend a hand, I'd sure appreciate it.

I'm not that good (yet) at DB design and optimized queries.

If you're able to help, please contact me off-line.

Thx

Walter

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: in need of guidance ...

Walter:

Since you're using an OpenSource package, you should have detailed
information on their schema for user/contact data.

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.

Mike

-----Original Message-----
From: phpWalter [mailto:phpWalter@torres.ws]
Sent: Wednesday, October 11, 2006 5:09 PM
To: win32@lists.mysql.com
Subject: in need of guidance ...

I'm in the process of specing radical changes to an opensource CRM package
I've been working with for the past few years.

I need guidance in regards to a schema for the user/contact data (name,
address, etc).

If anyone can lend a hand, I'd sure appreciate it.

I'm not that good (yet) at DB design and optimized queries.

If you're able to help, please contact me off-line.

Thx

Walter

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=techstuff@michaelmcbride. us




--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

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/win32?unsub=gcdmw-win32@m.gmane.org

RE: in need of guidance ...

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:phpWalter@torres.ws]
Sent: Wednesday, October 11, 2006 6:43 PM
To: win32@lists.mysql.com
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/win32?unsub=gcdmw-win32@m.gmane.org

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
randy@novaspace.com


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:phpWalter@torres.ws]
> Sent: Wednesday, October 11, 2006 6:43 PM
> To: win32@lists.mysql.com
> 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/win32?unsub=gcdmw-win32@m.gmane.org

RE: in need of guidance ...

On Wed, October 11, 2006 6:40 pm, 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.

One level they're not.

2 contacts:
Mark and Nancy McGee
123 Penguin Place, Apt 87.
Somewhere, USA 12345

In my "world" they have seperate "contact" records but share share the
same address record (street, etc).

But mail stops (usually) refer to a group of people within the same
devision of a company.

Mega Corp
Mark McGee
123 Penguin Place, MS 87
Somewhere, USA 12345

Mega Corp
Mark McGee
123 Penguin Place, MS 87
Somewhere, USA 12345

Mega Corp
Alice Cooper
123 Penguin Place, MS 23
Somewhere, USA 12345

3 contacts records, 1 address record, 2 mail stops and 1 company record.

Both contact and company record refer to the same address record.

I figure the mail stop data would be part of the contact record.

walter






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org