Was the thread title boring?

I got 0 responses to my "Opinions on db structure" post. That's painful.
Something about the title having to do with databases in a group like
this, way off :-)
Answer this time or I'll post it again... 8>)

Thanks.

=============== =============== =============== =======

I'm creating an Access 2003 field-based survey tool. The field users (50 of
them) submit survey results each week to a master db at the HQ.

Primary Key for field database is SurveyID, with a unique index on
PropertyID + SurveyDate.

When the field users submit the data, I'm going to send only the PropertyID
+ SurveyDate, and have the master db assign a new SurveyID for use within
that [Oracle] database.

So I'll end up with two SurveyIDs - one on the field user's system, and a
different one at HQ. I doubt anyone but me will ever see or use the
SurveyIDs.

Anyone think this is good or bad?

I could go with a composite PK of PropertyID + SurveyDate on both systems,
but this system will eventually have millions of records, so the single
SurveyID is more efficient.

I got 0 responses to my "Opinions on db structure" post. That's painful.

Answer this time or I'll post it again... 8>)

DFS wrote:

I got 0 responses to my "Opinions on db structure" post. That's
painful.
Something about the title having to do with databases in a group like
this, way off :-)

Answer this time or I'll post it again... 8>)

Thanks.

=============== =============== =============== =======

I'm creating an Access 2003 field-based survey tool. The field
users (50 of them) submit survey results each week to a master db at
the HQ.

Primary Key for field database is SurveyID, with a unique index on
PropertyID + SurveyDate.

When the field users submit the data, I'm going to send only the
PropertyID + SurveyDate, and have the master db assign a new
SurveyID for use within that [Oracle] database.

So I'll end up with two SurveyIDs - one on the field user's system,
and a different one at HQ. I doubt anyone but me will ever see or
use the SurveyIDs.

Anyone think this is good or bad?

I could go with a composite PK of PropertyID + SurveyDate on both
systems, but this system will eventually have millions of records,
so the single SurveyID is more efficient.

It's not so bad as long as your interface always uses the composite
keys for matching,

Right now the interface always uses the single key for matching.

the individual databases can use their own PK
internally. This would be the situation in most cases where two
systems exchnange data.
It's a one-way (field to HQ) data "exchange", and the field users won't be
downloading data.

You could implement some form of pool in the main database for the
remote users to use as PKs but as you already have a candidate key for
matching there's little point in complicating it further.
I agree. Don't need another source for the PK.

I think
Oracle is big enough and ugly enough to handle a few million rows
using a composite key as opposed to a single key when importing a few
rows from your guy's laptop.

I'm creating an Access 2003 field-based survey tool. The field users (50 of
them) submit survey results each week to a master db at the HQ.

Primary Key for field database is SurveyID, with a unique index on
PropertyID + SurveyDate.

When the field users submit the data, I'm going to send only the PropertyID
+ SurveyDate, and have the master db assign a new SurveyID for use within
that [Oracle] database.

So I'll end up with two SurveyIDs - one on the field user's system, and a
different one at HQ. I doubt anyone but me will ever see or use the
SurveyIDs.

Anyone think this is good or bad?

I could go with a composite PK of PropertyID + SurveyDate on both systems,
but this system will eventually have millions of records, so the single
SurveyID is more efficient.

"DFS" <no****@nospam. com> wrote in message
news:<10******* ******@corp.sup ernews.com>...

I'm creating an Access 2003 field-based survey tool. The field
users (50 of them) submit survey results each week to a master db at
the HQ.

Primary Key for field database is SurveyID, with a unique index on
PropertyID + SurveyDate.

When the field users submit the data, I'm going to send only the
PropertyID + SurveyDate, and have the master db assign a new
SurveyID for use within that [Oracle] database.

So I'll end up with two SurveyIDs - one on the field user's system,
and a different one at HQ. I doubt anyone but me will ever see or
use the SurveyIDs.

Anyone think this is good or bad?

I could go with a composite PK of PropertyID + SurveyDate on both
systems, but this system will eventually have millions of records,
so the single SurveyID is more efficient.
I understand why you want to have a single field (SurveyID) as a
foreign key to other tables. Why not let the field users create the
SurveyID in the field using autonumber?

I do - it does - they do.

If you are worried about
getting duplicate SurveyID's from different field users use the GUID
datatype (replication id) instead of random long integer.
I was going to use a new, sequential integer in the HQ database. The
scripts that load the field data would ignore the field SurveyID, and the
database would assign a new SurveyID. Anyone querying the HQ database would
then use that ID.

You could
also cobble together your own unique key from a combination of the
SurveyDate (maybe represented as # of days since 1/1/1900, easily
handled with 5 digits) and the PropertyID.