By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,567 Members | 1,592 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,567 IT Pros & Developers. It's quick & easy.

Was the thread title boring?

P: n/a
DFS
I got 0 responses to my "Opinions on db structure" post. That's painful.

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.

Thanks
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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, the individual databases can use their own PK internally.
This would be the situation in most cases where two systems exchnange 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 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.

--
This sig left intentionally blank
Nov 13 '05 #2

P: n/a
DFS wrote:
I got 0 responses to my "Opinions on db structure" post. That's painful.

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


I have one moment per day, normally, that I handle mail and news. Your
posts arrive together on my desk.

Sorry!
Nov 13 '05 #3

P: n/a
DFS
Trevor Best wrote:
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.


OK.
Thanks for your thoughts, Trevor.
Nov 13 '05 #4

P: n/a
"DFS" <no****@nospam.com> wrote in message news:<10*************@corp.supernews.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? If you are worried about
getting duplicate SurveyID's from different field users use the GUID
datatype (replication id) instead of random long integer. 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.

Bruce

Bruce
Nov 13 '05 #5

P: n/a
DFS
Bruce wrote:
"DFS" <no****@nospam.com> wrote in message
news:<10*************@corp.supernews.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.


Not a bad idea.
Thanks
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.