Key Fields

#1

FOR PARADOX 8

I'm making a name and address database (no phone) with a dozen fields; there
would be about 500 records. The sequence is: Last
Name, First Name, Organization, Street, City, etc. Field type is A
for all fields. I'm assigning a key to Last Name and First Name; Organization
is not a good bet for a duplicate-breaker (thus having
a key ) because very few records will use Organization. I could
assign a key to Street, but Organization is between First Name and Street,
putting Street out of sequence.

I could have the sequence as Last Name, First Name, Street, Organization--and
assign a key to the first three. The mailing label Expert would allow me
to put Street and Organizaiton where they
belong for mailing labels.

Please comment.

#2

If you're only going to have 500 records, the three-field key is
probably OK, but best practice would be to have a meaningless integer
key field with a secondary index on Last Name and First Name, another on
Organization, maybe one on State, and possibly others depending on how
you're going to use the data.

If you're going to have any secondary indexes at all, the compound key
you propose could be problematic; I say "could be" because I used
compound keys extensively in some of my early Paradox databases w/o too
much issue. If you add any tables with relations to your address table,
the compound key becomes much more of an issue since you'd need to
include all three fields in any other related table.

Delbert wrote:

Quote:

FOR PARADOX 8

I'm making a name and address database (no phone) with a dozen fields; there
would be about 500 records. The sequence is: Last
Name, First Name, Organization, Street, City, etc. Field type is A
for all fields. I'm assigning a key to Last Name and First Name; Organization
is not a good bet for a duplicate-breaker (thus having
a key ) because very few records will use Organization. I could
assign a key to Street, but Organization is between First Name and Street,
putting Street out of sequence.

I could have the sequence as Last Name, First Name, Street, Organization--and
assign a key to the first three. The mailing label Expert would allow me
to put Street and Organizaiton where they
belong for mailing labels.

Please comment.

#3

"I could assign a key to Street, but Organization is between First Name and
Street, putting Street out of sequence." Jeff, would a key field out
of sequence work?

Jeff Shoaf <jeffshoaf (AT) alltel (DOT) net> wrote:

Quote:

If you're only going to have 500 records, the three-field key is
probably OK, but best practice would be to have a meaningless integer
key field with a secondary index on Last Name and First Name, another on

Organization, maybe one on State, and possibly others depending on how
you're going to use the data.

If you're going to have any secondary indexes at all, the compound key
you propose could be problematic; I say "could be" because I used
compound keys extensively in some of my early Paradox databases w/o too

much issue. If you add any tables with relations to your address table,

the compound key becomes much more of an issue since you'd need to
include all three fields in any other related table.

Delbert wrote:
FOR PARADOX 8

I'm making a name and address database (no phone) with a dozen fields;
there
would be about 500 records. The sequence is: Last
Name, First Name, Organization, Street, City, etc. Field type is A
for all fields. I'm assigning a key to Last Name and First Name; Organization
is not a good bet for a duplicate-breaker (thus having
a key ) because very few records will use Organization. I could
assign a key to Street, but Organization is between First Name and Street,
putting Street out of sequence.

I could have the sequence as Last Name, First Name, Street, Organization--and
assign a key to the first three. The mailing label Expert would allow
me
to put Street and Organizaiton where they
belong for mailing labels.

Please comment.

#4

Delbert,

Using the Street address as part of your tables KEY, you will stand a good
chance of creating duplicates.

Look at these 2 addresses:

1234 W. Sassafras Rd.
1234 Sasafras Road West

These 2 addresses are the same location but entered differently and also
misspelled. You table would accept these as Different addresses.

To solve this, I created a library that reduces street addresses to a
numeric value. It compensates for mis-spelled words (English) and also for
different Address Formats.

I would include a new Field and place the numeric value of the address in
that field... Use this field in combination with say your First Name field
to create a compound KEY.

The code for this can be found at:

http://www.ars-florida.com/PDOXboard

Look at article # 3, "StreetPX"

--
....
`·.¸¸.·´¯`·.¸¸.·´¯`·-> rodney

#5

The key must be the first field(s) in the table. I'd recommend using a
meaningless integer key.

Delbert wrote:

Quote:

"I could assign a key to Street, but Organization is between First Name and
Street, putting Street out of sequence." Jeff, would a key field out
of sequence work?

Jeff Shoaf <jeffshoaf (AT) alltel (DOT) net> wrote:
If you're only going to have 500 records, the three-field key is
probably OK, but best practice would be to have a meaningless integer
key field with a secondary index on Last Name and First Name, another on

Organization, maybe one on State, and possibly others depending on how
you're going to use the data.

If you're going to have any secondary indexes at all, the compound key
you propose could be problematic; I say "could be" because I used
compound keys extensively in some of my early Paradox databases w/o too

much issue. If you add any tables with relations to your address table,

the compound key becomes much more of an issue since you'd need to
include all three fields in any other related table.

Delbert wrote:
FOR PARADOX 8

I'm making a name and address database (no phone) with a dozen fields;
there
would be about 500 records. The sequence is: Last
Name, First Name, Organization, Street, City, etc. Field type is A
for all fields. I'm assigning a key to Last Name and First Name; Organization
is not a good bet for a duplicate-breaker (thus having
a key ) because very few records will use Organization. I could
assign a key to Street, but Organization is between First Name and Street,
putting Street out of sequence.

I could have the sequence as Last Name, First Name, Street, Organization--and
assign a key to the first three. The mailing label Expert would allow
me
to put Street and Organizaiton where they
belong for mailing labels.

Please comment.

#6

How would "a meaningless integer key" work?

Separately, from my previous experience (about 12 years) making mailing labels
on the same general database, I never had two
identical names, such as John W. Smith. Also, to keep things
simple, should there now be two identical John W. Smiths, one of
them could be set off with a lack of a period after the W or by some similar
inconspicuous trick.

Jeff Shoaf <jeffshoaf (AT) alltel (DOT) net> wrote:

Quote:

The key must be the first field(s) in the table. I'd recommend using a
meaningless integer key.

Delbert wrote:
"I could assign a key to Street, but Organization is between First Name
and
Street, putting Street out of sequence." Jeff, would a key field out
of sequence work?

Jeff Shoaf <jeffshoaf (AT) alltel (DOT) net> wrote:
If you're only going to have 500 records, the three-field key is
probably OK, but best practice would be to have a meaningless integer

key field with a secondary index on Last Name and First Name, another
on

Organization, maybe one on State, and possibly others depending on how

you're going to use the data.

If you're going to have any secondary indexes at all, the compound key

you propose could be problematic; I say "could be" because I used
compound keys extensively in some of my early Paradox databases w/o too

much issue. If you add any tables with relations to your address table,

the compound key becomes much more of an issue since you'd need to
include all three fields in any other related table.

Delbert wrote:
FOR PARADOX 8

I'm making a name and address database (no phone) with a dozen fields;
there
would be about 500 records. The sequence is: Last
Name, First Name, Organization, Street, City, etc. Field type is A
for all fields. I'm assigning a key to Last Name and First Name;
Organization
is not a good bet for a duplicate-breaker (thus having
a key ) because very few records will use Organization. I could
assign a key to Street, but Organization is between First Name and Street,
putting Street out of sequence.

I could have the sequence as Last Name, First Name, Street, Organization--and
assign a key to the first three. The mailing label Expert would allow
me
to put Street and Organizaiton where they
belong for mailing labels.

Please comment.

#7

On 21 Mar 2007 22:39:06 -0500, Delbert wrote:

Quote:

How would "a meaningless integer key" work?

Separately, from my previous experience (about 12 years) making mailing labels
on the same general database, I never had two
identical names, such as John W. Smith. Also, to keep things
simple, should there now be two identical John W. Smiths, one of
them could be set off with a lack of a period after the W or by some similar
inconspicuous trick.

Jeff Shoaf <jeffshoaf (AT) alltel (DOT) net> wrote:
The key must be the first field(s) in the table. I'd recommend using a
meaningless integer key.

You wouldn't want to do business with my Dad and me. Our names are
identical. And at one time we had the same address.

Also, people change their names, yet remain the same person. Women do this
when they get married (50% of the population) and do it again when they get
divorced (25% of the population).

Longint keys are strongly recommended. Basically, you just give everyone a
unique, arbitrary ID number for internal use. This number never changes,
ever, for any reason whatsoever. The first person is 1, the second person
is 2, and so forth. Using longints allows you to put more than 33,000
people in your database. Ya never know.

Jim Hargan

#8

I strongly suggest you look at my database Basics paper on our paradox resources
page. Link in my signature.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since 1982

#9

Jim,

Quote:

Using longints allows you to put more than 33,000
people in your database. Ya never know.

Sorry for correcting you, Jim, but smallInt's give you up to 32,768 people,
and longInt's give you over 2 million.

Jim Moseley

#10

On 22 Mar 2007 07:29:17 -0500, "Jim Moseley"
<jmose (AT) mapson (DOT) attglobal.net> wrote:

Quote:

Jim,

Using longints allows you to put more than 33,000
people in your database. Ya never know.

Sorry for correcting you, Jim, but smallInt's give you up to 32,768 people,
and longInt's give you over 2 million.

Change "million" to "billion".

Bill

Quote:

Jim Moseley

----------------------------------------------------------------
Reverse halves of the user name for my e-address