Re: Autonum field for Relationships and Replication

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

MichKa, yeah? I was wondering why I haven't seen his postings for a while...

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
"David W. Fenton" <dXXXfenton@xxxxxxxxxxxxxxxx> wrote in message
news:Xns9694DB219D7Edfentonbwaynetinvali@xxxxxxxxxxxxxxxx
"Cheval" <NoSpam.Ask@xxxxxxxxxxxx> wrote in
news:7_XBe.49658$oJ.14659@xxxxxxxxxxxxxxxxxxxxxxxxxx:

> "You mean you don't use indexes in the table definitions to
> properly restrict entries in candidate keys that could have been
> the PK?" No. I use indexes in the fields that need them. I mean
> that due to the PK being a single autonumber field, if the true
> key is multi-column then I enforce the multi-column uniqueness in
> code.

Why? To me, that's risking your database because it depends on the
application to maintain data integrity. Data integrity should be
maintained by the database engine, as part of the schema definition
(including indexes).

> Actually you got me thinking. I just created three tables in a
> temp database. Two tables with multi-column PK fields and an
> non-duplicate autonumber field and a third with the autonumber
> foreign fields from the other two. While it doesn't look the way I
> expect in the relationships window it does work and saves me from
> the constraints coding. I shall give that good consideration in
> future Access databases. Thanks.

It would be what I consider to be standard practice.

Now, if you don't have a real candidate key that can be enforced by
an index, then, lacking triggers, you will need to use code to
enforce your definition of uniqueness. This is a very common
scenario in tables storing data on people, since applications often
have the requirement to be able to create a record for a person
without having the complete data that would be needed to insure
uniqueness in an index (i.e., fields in the candidate key need to
allow nulls, which means no unique index).

> ""> Option A: Add a new autonumber PK to every table.""
> "Every table? Why?...But Penny did not say she was generating the
> sequential PK for anything but a single table, tblCandidates. So,
> I think you presumption that there's any need to add an Autonumber
> PK to anything but the one table is mistaken."
> As per Penny's response "I am going with adding the Autonumber to
> all pertinent tables (which I will ALWAYS do in future)"

Well, I was surprised at that one. I don't know why anyone would use
a generated sequence for fields that aren't exposed to users, and in
most data hierarchies, the PK of the child tables just doesn't
matter. So, for replication, that means the random autonumber
doesn't cause a problem, and it also means that before replication,
there's no reason to program your sequence.

So, that's what confused me -- I couldn't imagine a scenario where
all tables other than the one she mentioned would have a generated
sequence. Yes, it's not uncommon to have a couple or three such
tables in an app, but certainly not *all* the tables.

To me, then, it looks like her original schema design was quite
unusual.

>> Work? Lots. Plus you also now have to create the table
>> constraints in code. . . .
> "Eh? What table constraints are you talking about? "
>
> As she said that she will change other tables as well, then if she
> changes tables with multi-column keys, then yes this will be the
> case.

But not in *code*. Yes, she may end up with a useless generated
sequence if she doesn't change the code, but the "table constraints"
should not have been enforced in code in the first place, but in the
table and RI definitions.

>> . . . Risks? High. Too many to list, and when do you update the
>> front end for the new design? You have to kick the users out
>> until your finished. . . .
> "Excuse me?"
>
> Again you answered your own question. There are a lot of steps
> that need to take place in certain sequences and many places to
> make a mistake that can create headaches for Penny and the users.

But none of those steps are unique to a replicated back end -- all
of them apply in one degree or another to every database application
in which you are applying a schema update.

> "Are you trying to confuse the matter here or are you trying to
> help? From where I sit, it doesn't look like the latter."
>
> Oh well, I guess then that I hope to help Penny and confuse you.
> :) But at least your thinking and keeping me on my toes.

Well, I'm glad you answer questions in the group. Since MichKa has
moved on to internationalization, somebody has to keep the flame
alive.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc


.

Relevant Pages

  • Re: Renumbering records
    ... use a two-column sequence instead, ... In fact, I don't really want true replication, but ... started getting erratic results in my autonumber field. ... Then the next time you copy an Access database to ...
    (microsoft.public.access.replication)
  • Re: Renumbering records
    ... Autonumber is just a special kind of default value, ... represent the sequence in which the records were added. ... I wanted to maintain that order and I thought the database ... That would be a regular replica, ...
    (microsoft.public.access.replication)
  • Re: Autonum field for Relationships and Replication
    ... > that due to the PK being a single autonumber field, ... that's risking your database because it depends on the ... if you don't have a real candidate key that can be enforced by ... there's no reason to program your sequence. ...
    (microsoft.public.access.replication)
  • Re: Missign Autonumber records
    ... Autonumbers will have gaps in the sequence because, once "used", a number ... >I have a table setup to list incidents and the primary key is an autonumber ... The database is currently setup (I know this isn't the best ...
    (microsoft.public.access.tablesdbdesign)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)