Direction needed with a field conundrum

[Jeff Conrad]

Hi,

I'm helping a local county office with a fairly big project. This county office does Assessment,
Taxation, Survey, etc. We're just at the beginning stages of setting up tables in Access. Tables
will eventually be upsized to SQL server. We're also going to have to migrate 15+ years of badly
designed flat file AS400 data tables into the new table structure. Lots of orphan data, redundant
information, etc. Lots of cleanup work will need to be done; not fun.

I'm having some difficulty with some field arrangements for a table of "Owners." An owner can have
many Parcels (another table) and a Parcel can have more than owner (M:M of course). My trouble is
setting up the fields for the *names* in the Owner table.

An owner, for example could be a person: John M. Doe.
However, an owner could also be a company entity: Wyzard Enterprises, Inc.
Also, an owner could very well be a husband AND wife: John M. Doe and Jane D. Doe

Right now the owner information is ALL stored in one field. What I'm trying to figure out is do I
have fields for first, last, middle name AND a field for a company name if applicable? Something
like so:

OwnerID
FirstName
MiddleName
LastName
CompanyName

Or is this not the way to go? A different arrangement???
Thanks for any comments,

 

[Ken Snell [MVP]]

Jeff -

One way to approach a decision is to consider what you'll do with the name
field data. Will you be needing to search on those names, say, last names?
Will you need to join a part of the name to a field in another table, say, a
table of owners' last names where an owner last name record would be related
to a parcel? Or will the name just be displayed in most cases?

If the name parts won't be used to join other tables, then I'd be inclined
to consider using a single field for the entire "owner" information in terms
of name. You can always do a search of that field for a specific last name
or company name if you wanted, though it might be slow for a large database.

Otherwise, you'll have to make some decisions about how much atomicity
you'll want to be able to have for the name data, and then design the table
accordingly.

My experience with similar things has been that trying to figure out how to
separate these data into all possible fields that I can envision wanting
(e.g., first name, middle name, last name, first company name, subsidiary
company name, "dba" name, suffix for business corporate status [e.g., LLC,
Inc., etc.], identifier suffix for person [e.g., Ph.D., Jr.], descriptor for
person [e.g., Dr., etc.], etc.) can become daunting. Legal names, which are
what are used for parcel ownership, etc., can be quite different in
structure and content than "normal" names.

Just my 2 cents suggestions....
--

Ken Snell
<MS ACCESS MVP>

 

[Jeff Conrad]

Hi Ken,

I appreciate your comments and time.

Yes, I do believe we will be needing to make frequent searches on the owner's *name*, whatever that
may be. Reports and queries will be needed to run to see what parcels this person or entity owns,
past sales, history, etc.

Currently this information is in one field and quite frankly is a huge mess, particularly with
company names. A large part of the issue comes from simple punctuation errors.

For example, there are records that look just like this:
Ken Snell, LLC.
Ken Snell LLC (notice no comma or period)
Ken Snell, LLC (notice no period)
Ken Snell, LLC. (notice the single space in front)

In actuality all four of these entities are exactly the same company, but to the existing AS400 data
tables they are four completely different owners! Not good!

A similar problem arises with individuals:
Ken Snell
Mrs. Snell
Ken and Mrs. Snell

There are times where simple human error has made *bad* records, but in some cases the above three
record are legitimate. Mr. Snell has some property in his name, Mrs. Snell has property in her own
name, and they also have property together. This actually happens more often than I thought it
would.

So I am in a quandary about the best way to proceed. Part of me believes that it may be best to only
have one field for the owner 'name' in this table, but I'm still not convinced. I do not think we
will need to join a part of the name to a field in another table, but we are still in the initial
beginning phases at this point.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff -

One way to approach a decision is to consider what you'll do with the name
field data. Will you be needing to search on those names, say, last names?
Will you need to join a part of the name to a field in another table, say, a
table of owners' last names where an owner last name record would be related
to a parcel? Or will the name just be displayed in most cases?

If the name parts won't be used to join other tables, then I'd be inclined
to consider using a single field for the entire "owner" information in terms
of name. You can always do a search of that field for a specific last name
or company name if you wanted, though it might be slow for a large database.

Otherwise, you'll have to make some decisions about how much atomicity
you'll want to be able to have for the name data, and then design the table
accordingly.

My experience with similar things has been that trying to figure out how to
separate these data into all possible fields that I can envision wanting
(e.g., first name, middle name, last name, first company name, subsidiary
company name, "dba" name, suffix for business corporate status [e.g., LLC,
Inc., etc.], identifier suffix for person [e.g., Ph.D., Jr.], descriptor for
person [e.g., Dr., etc.], etc.) can become daunting. Legal names, which are
what are used for parcel ownership, etc., can be quite different in
structure and content than "normal" names.

Just my 2 cents suggestions....
--

Ken Snell
<MS ACCESS MVP>

Hi,

I'm helping a local county office with a fairly big project. This county office does Assessment,
Taxation, Survey, etc. We're just at the beginning stages of setting up tables in Access. Tables
will eventually be upsized to SQL server. We're also going to have to migrate 15+ years of badly
designed flat file AS400 data tables into the new table structure. Lots of orphan data, redundant
information, etc. Lots of cleanup work will need to be done; not fun.

I'm having some difficulty with some field arrangements for a table of "Owners." An owner can have
many Parcels (another table) and a Parcel can have more than owner (M:M of course). My trouble is
setting up the fields for the *names* in the Owner table.

An owner, for example could be a person: John M. Doe.
However, an owner could also be a company entity: Wyzard Enterprises, Inc.
Also, an owner could very well be a husband AND wife: John M. Doe and Jane D. Doe

Right now the owner information is ALL stored in one field. What I'm trying to figure out is do I
have fields for first, last, middle name AND a field for a company name if applicable? Something
like so:

OwnerID
FirstName
MiddleName
LastName
CompanyName

Or is this not the way to go? A different arrangement???
Thanks for any comments,

 

[Ken Snell [MVP]]

Suppose you have an owner named Jeff Conrad. Is that always going to be the
same Jeff Conrad for all parcels where his name appears? How will you
uniquely identify *the* Jeff Conrad who owns the parcel of land in the NW
corner of the municipality, as compared to Jeff Conrad who owns the parcel
of land in the SE corner?

And what about the possibility that Jeff Conrad also is the same Jeffrey L.
Conrad who owns two additional parcels?

How will you ensure that they are the same person? My experience in
government indicates that this will be difficult, as the only "identifier"
that would come to mind is the address of the owner, but people don't
necessarily keep address listings up to date with municipalities (except
with some exception for tax billings).

The typos that you're seeing will not disappear with a new db. Especially as
it's possible for the paperwork to have errors in it which no one will
notice when entering data into the db.

As you note, of course I can own property on my own, as well as own property
with my wife, or with my son, etc. And of course unfortunately legal
relationships can change over time (marriage, divorce, corporate breakups,
corporate mergers), so tracking entities can be very difficult, especially
as those entities are not defined by the database -- the db is just trying
to "model" what others control.

If you were starting from scratch, you'd be able to assign unique
identifiers to each owner in some way that would potentially allow you to
track that same owner, although I have my doubts about that.

I feel the apprehension, as putting it all into one field seems to violate
normalization rules that we use elsewhere. But I still think a single field
will be the better choice in this case.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I appreciate your comments and time.

Yes, I do believe we will be needing to make frequent searches on the owner's *name*, whatever that
may be. Reports and queries will be needed to run to see what parcels this person or entity owns,
past sales, history, etc.

Currently this information is in one field and quite frankly is a huge mess, particularly with
company names. A large part of the issue comes from simple punctuation errors.

For example, there are records that look just like this:
Ken Snell, LLC.
Ken Snell LLC (notice no comma or period)
Ken Snell, LLC (notice no period)
Ken Snell, LLC. (notice the single space in front)

In actuality all four of these entities are exactly the same company, but to the existing AS400 data
tables they are four completely different owners! Not good!

A similar problem arises with individuals:
Ken Snell
Mrs. Snell
Ken and Mrs. Snell

There are times where simple human error has made *bad* records, but in some cases the above three
record are legitimate. Mr. Snell has some property in his name, Mrs. Snell has property in her own
name, and they also have property together. This actually happens more often than I thought it
would.

So I am in a quandary about the best way to proceed. Part of me believes that it may be best to only
have one field for the owner 'name' in this table, but I'm still not convinced. I do not think we
will need to join a part of the name to a field in another table, but we are still in the initial
beginning phases at this point.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff -

One way to approach a decision is to consider what you'll do with the name
field data. Will you be needing to search on those names, say, last names?
Will you need to join a part of the name to a field in another table, say, a
table of owners' last names where an owner last name record would be related
to a parcel? Or will the name just be displayed in most cases?

If the name parts won't be used to join other tables, then I'd be inclined
to consider using a single field for the entire "owner" information in terms
of name. You can always do a search of that field for a specific last name
or company name if you wanted, though it might be slow for a large database.

Otherwise, you'll have to make some decisions about how much atomicity
you'll want to be able to have for the name data, and then design the table
accordingly.

My experience with similar things has been that trying to figure out how to
separate these data into all possible fields that I can envision wanting
(e.g., first name, middle name, last name, first company name, subsidiary
company name, "dba" name, suffix for business corporate status [e.g., LLC,
Inc., etc.], identifier suffix for person [e.g., Ph.D., Jr.], descriptor for
person [e.g., Dr., etc.], etc.) can become daunting. Legal names, which are
what are used for parcel ownership, etc., can be quite different in
structure and content than "normal" names.

Just my 2 cents suggestions....
--

Ken Snell
<MS ACCESS MVP>

Hi,

I'm helping a local county office with a fairly big project. This

county
office does Assessment,

Taxation, Survey, etc. We're just at the beginning stages of setting

up
tables in Access. Tables

will eventually be upsized to SQL server. We're also going to have to migrate 15+ years of badly
designed flat file AS400 data tables into the new table structure.

Lots of
orphan data, redundant

information, etc. Lots of cleanup work will need to be done; not fun.

I'm having some difficulty with some field arrangements for a table of "Owners." An owner can have
many Parcels (another table) and a Parcel can have more than owner

(M:M of
course). My trouble is

setting up the fields for the *names* in the Owner table.

An owner, for example could be a person: John M. Doe.
However, an owner could also be a company entity: Wyzard Enterprises, Inc.
Also, an owner could very well be a husband AND wife: John M. Doe and

Jane
D. Doe

Right now the owner information is ALL stored in one field. What I'm trying to figure out is do I
have fields for first, last, middle name AND a field for a company

name if
applicable? Something

like so:

OwnerID
FirstName
MiddleName
LastName
CompanyName

Or is this not the way to go? A different arrangement???
Thanks for any comments,

 

[Jeff Conrad]

Hi Ken,

Thanks for your time and input.
Comments below....

Suppose you have an owner named Jeff Conrad. Is that always going to be the
same Jeff Conrad for all parcels where his name appears? How will you
uniquely identify *the* Jeff Conrad who owns the parcel of land in the NW
corner of the municipality, as compared to Jeff Conrad who owns the parcel
of land in the SE corner?

Yes, these are the problems I am already thinking about.

And what about the possibility that Jeff Conrad also is the same Jeffrey L.
Conrad who owns two additional parcels?

Yep, same type of problems.

How will you ensure that they are the same person? My experience in
government indicates that this will be difficult, as the only "identifier"
that would come to mind is the address of the owner, but people don't
necessarily keep address listings up to date with municipalities (except
with some exception for tax billings).

Exactly!
And from what I've been told whatever is on the Deed is EXACTLY how it must appear in the database.
The Deed is a legal document and therefore you cannot assume anything and must take the exact
wording used on the paper forms.

The typos that you're seeing will not disappear with a new db. Especially as
it's possible for the paperwork to have errors in it which no one will
notice when entering data into the db.

Yes, I know we can never completely eliminate the human data entry errors. I was thinking though
they could be reduced for sure by breaking apart the fields. It's a nightmare to look at some of the
raw data and see this:
Ken Snell And Mrs. Snell
Snell, Ken and Snell, Mrs.
etc.
:-(
I suppose if we need to do a search of all the properties you own we could do a query on that field
for any instance of "Snell."

As you note, of course I can own property on my own, as well as own property
with my wife, or with my son, etc. And of course unfortunately legal
relationships can change over time (marriage, divorce, corporate breakups,
corporate mergers), so tracking entities can be very difficult, especially
as those entities are not defined by the database -- the db is just trying
to "model" what others control.
Agreed.

If you were starting from scratch, you'd be able to assign unique
identifiers to each owner in some way that would potentially allow you to
track that same owner, although I have my doubts about that.

My thoughts as well.
Unfortunately we will have to be migrating over the AS400 data in these new table structures. Fun,
fun, fun.

I feel the apprehension, as putting it all into one field seems to violate
normalization rules that we use elsewhere. But I still think a single field
will be the better choice in this case.

I'm really starting to lean that way now the more I think about this issue. This may very well be
why the owner name field was originally created with just the one field. I know this violates
normalization rules and that is why I'm having a hard time coming to grips with that reality. I feel
a pit in my stomach, but it just may have to be this way.

You may see several posts on this project in the next few months.
(Would you believe this is my Tutoring project?! Geez!)

--
Jeff Conrad
Access Junkie
Bend, Oregon

Hi Ken,

I appreciate your comments and time.

Yes, I do believe we will be needing to make frequent searches on the owner's *name*, whatever that
may be. Reports and queries will be needed to run to see what parcels this person or entity owns,
past sales, history, etc.

Currently this information is in one field and quite frankly is a huge mess, particularly with
company names. A large part of the issue comes from simple punctuation errors.

For example, there are records that look just like this:
Ken Snell, LLC.
Ken Snell LLC (notice no comma or period)
Ken Snell, LLC (notice no period)
Ken Snell, LLC. (notice the single space in front)

In actuality all four of these entities are exactly the same company, but to the existing AS400 data
tables they are four completely different owners! Not good!

A similar problem arises with individuals:
Ken Snell
Mrs. Snell
Ken and Mrs. Snell

There are times where simple human error has made *bad* records, but in some cases the above three
record are legitimate. Mr. Snell has some property in his name, Mrs. Snell has property in her own
name, and they also have property together. This actually happens more often than I thought it
would.

So I am in a quandary about the best way to proceed. Part of me believes that it may be best to only
have one field for the owner 'name' in this table, but I'm still not convinced. I do not think we
will need to join a part of the name to a field in another table, but we are still in the initial
beginning phases at this point.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Jeff -

One way to approach a decision is to consider what you'll do with the name
field data. Will you be needing to search on those names, say, last names?
Will you need to join a part of the name to a field in another table, say, a
table of owners' last names where an owner last name record would be related
to a parcel? Or will the name just be displayed in most cases?

If the name parts won't be used to join other tables, then I'd be inclined
to consider using a single field for the entire "owner" information in terms
of name. You can always do a search of that field for a specific last name
or company name if you wanted, though it might be slow for a large database.

Otherwise, you'll have to make some decisions about how much atomicity
you'll want to be able to have for the name data, and then design the table
accordingly.

My experience with similar things has been that trying to figure out how to
separate these data into all possible fields that I can envision wanting
(e.g., first name, middle name, last name, first company name, subsidiary
company name, "dba" name, suffix for business corporate status [e.g., LLC,
Inc., etc.], identifier suffix for person [e.g., Ph.D., Jr.], descriptor for
person [e.g., Dr., etc.], etc.) can become daunting. Legal names, which are
what are used for parcel ownership, etc., can be quite different in
structure and content than "normal" names.

Just my 2 cents suggestions....
--

Ken Snell
<MS ACCESS MVP>




Hi,

I'm helping a local county office with a fairly big project. This county
office does Assessment,
Taxation, Survey, etc. We're just at the beginning stages of setting up
tables in Access. Tables
will eventually be upsized to SQL server. We're also going to have to
migrate 15+ years of badly
designed flat file AS400 data tables into the new table structure. Lots of
orphan data, redundant
information, etc. Lots of cleanup work will need to be done; not fun.

I'm having some difficulty with some field arrangements for a table of
"Owners." An owner can have
many Parcels (another table) and a Parcel can have more than owner (M:M of
course). My trouble is
setting up the fields for the *names* in the Owner table.

An owner, for example could be a person: John M. Doe.
However, an owner could also be a company entity: Wyzard Enterprises, Inc.
Also, an owner could very well be a husband AND wife: John M. Doe and Jane
D. Doe

Right now the owner information is ALL stored in one field. What I'm
trying to figure out is do I
have fields for first, last, middle name AND a field for a company name if
applicable? Something
like so:

OwnerID
FirstName
MiddleName
LastName
CompanyName

Or is this not the way to go? A different arrangement???
Thanks for any comments,

 

[Joan Wild]

Yes, these are the problems I am already thinking about.


Yep, same type of problems.

Exactly!
And from what I've been told whatever is on the Deed is EXACTLY how
it must appear in the database. The Deed is a legal document and
therefore you cannot assume anything and must take the exact wording
used on the paper forms.

It's this point that struck me. Since your database is really dealing with
the legal document then that is what you are modeling. Maybe the above
points about whether it's the same person or not, does not matter. Your
database is not about people, but about the deeds. I would be inclined to
have a field for the deed name (whatever it is and however it is spelled).

Even if one person owns two properties, you can never make the assumption
that they do (even with a paper based system).

Yes, I know we can never completely eliminate the human data entry
errors. I was thinking though they could be reduced for sure by
breaking apart the fields. It's a nightmare to look at some of the
raw data and see this:
Ken Snell And Mrs. Snell
Snell, Ken and Snell, Mrs.

But if that's what 2 deeds say, then that is what you must record.

 

[Jeff Conrad]

Hi Joan,

Thanks for your input.
Comments below...

It's this point that struck me. Since your database is really dealing with
the legal document then that is what you are modeling. Maybe the above
points about whether it's the same person or not, does not matter. Your
database is not about people, but about the deeds. I would be inclined to
have a field for the deed name (whatever it is and however it is spelled).

There is a Deed table involved in the setup by the way.
Not sure if I mentioned this before.

Even if one person owns two properties, you can never make the assumption
that they do (even with a paper based system).

I definitely agree on this point.
What clouds the subject slightly is that this is for a small county (not the one I am in) where
literally everyone does know each other!
So if ANY person in the county looked in this database and saw:
Joan Wild
Wild, Joan
Joan B. Wild
Everyone would say, "Yep partnr', that'd be that Joan lady down by the river, just past ol' man
Peabody's shack."
<vbg>

Yes, I do understand your point though.

But if that's what 2 deeds say, then that is what you must record.

I do believe this may be the case then.
So is it your conclusion as well that the Owner name (whatever it may be) should be in one field as
well?

 

[Joan Wild]

I definitely agree on this point.
What clouds the subject slightly is that this is for a small county
(not the one I am in) where literally everyone does know each other!
So if ANY person in the county looked in this database and saw:
Joan Wild
Wild, Joan
Joan B. Wild
Everyone would say, "Yep partnr', that'd be that Joan lady down by
the river, just past ol' man Peabody's shack."
<vbg>

(I've decided not to touch that.)

Yes, I do understand your point though.

Where is John and his examples - How do you know for certain that Joan Wild
and Wild, Joan are the same person - perhaps one is my daughter? I believe
it was George Forman who name all his sons George, and they live in the same
place.

I do believe this may be the case then.
So is it your conclusion as well that the Owner name (whatever it may
be) should be in one field as well?

That's my opinion based on what you've told us, yes. I haven't heard a
business reason for doing otherwise.

 

[Jeff Conrad]

Hi Joan,

(I've decided not to touch that.)


Where is John and his examples - How do you know for certain that Joan Wild
and Wild, Joan are the same person - perhaps one is my daughter? I believe
it was George Forman who name all his sons George, and they live in the same
place.

Don't worry, I have seen John's examples many times, "Names are not unique."
I got the point, trust me.

That's my opinion based on what you've told us, yes. I haven't heard a
business reason for doing otherwise.

Ok, that's what I am now thinking as well.
This may change when I have more information, but for now I believe this is the direction we should
follow.

Thanks for your time and input.