Non-unique AutoNumber

[Brian]

I am helping someone redesign a database, and the original designer was
having problems with one existing table because it has a multiple key
involving a non-unique AutoNumber. That is, the primary key consists of two
fields:

1. Field1: AutoNumber, Indexed, Duplicates OK
2. Field2: Text, Indexed, Duplicates OK

I think I know how I want to resolve the problem (split it into two tables -
Table1 with a true unique Field1 that acts as a foreign key to Field1 in
Table2 that also contains Field2), but I cannot seem to find any
documentation on how the AutoNumber would function in the existing case.

The existing data has duplicates in the AutoNumber field, which is certainly
allowable based on the Indexed (DuplicatesOK) property, but how could they
even get there with an AutoNumber?

Maybe I'm just limited in my experience, but when might one need to use
AutoNumber with Indexed (DuplicatesOK)?

 

[Tim Ferguson]

I am helping someone redesign a database, and the original designer
was having problems with one existing table because it has a multiple
key involving a non-unique AutoNumber. That is, the primary key
consists of two fields:

1. Field1: AutoNumber, Indexed, Duplicates OK
2. Field2: Text, Indexed, Duplicates OK

...
Maybe I'm just limited in my experience, but when might one need to
use AutoNumber with Indexed (DuplicatesOK)?

One would have to go out of one's way to create non-unique values in an
AN column; it's not impossible but it's hard work.

And no, I cannot think of a single reason why anyone would want to do
such a funny thing.

I think I know how I want to resolve the problem (split it into two
tables - Table1 with a true unique Field1 that acts as a foreign key
to Field1 in Table2 that also contains Field2),

Whoa: hold on there. Reasons for splitting a table into tables are about
the semantics of the things you are modelling. If there is a real hidden
one-to-many relationship between two entities in there (certainly
believable), then you need to split it. If it's only about the
convenience of picking a key for the table then think very hard before
creating more work for yourself.

but I cannot seem to
find any documentation on how the AutoNumber would function in the
existing case.

Well, an autonumber is just an autonumber: it functions the same in all
cases. It produces a new number for every record created; usually with a
value of one greater than the last one generated. When a table is
compacted it resets to one greater than the largest value in the table.

Hope that helps


Tim F

 

[George Nicholson]

Others will probably contribute, but the only thing I can think of is that
records were appended to this table from an external source and those
records already had values in the Autonumber field. That's the only way I
can think of for an Autonumber field to even have duplicates: the dupes came
from an external source. (Or existing data (with dupes) was appended to an
empty table when it was first created.)

I can't think of any circumstances where it would be desirable, but maybe
I'm lacking in imagination <g>.

HTH,

 

[Brian]

I just wanted a second opinion before I told the original designer that I
could not think of any reason to use a non-unique AutoNumber. It sounds like
I'm in good company. I am just offering help & advice as needed on this
project, and I am trying not to reinvent the wheel for the original developer
(e.g. redesign the underlying table structure from the ground up).

One point to clarify, however. I don't think this is just about convenience,
but I can certainly stand to be corrected. Field1 is a base contract number,
and Field2 is the type of contract (there are several types), so that a group
of contracts is tied together by their master number. The master contract
number, however, has no other function. We wanted Field1 to be AutoNumbered
to avoid conflicts between users concurrently creating new master contracts.

The existing table needs to have unique Field1/Field2 combinations; thus the
multi-key (not sure if that's the right term for a PK involving multiple
fields). I think I can see that the original developer was attempting to kill
two birds with one stone by:

1. Having an AutoNumber to ensure that each new record got a new base
contract number, but
2. Allow that base number to be used once with each contract type.

As you can see, this is not workable. Should I just drop the AutoNumber &
manually increment Field1 using a DMax(Fields1)+1 on the current table, with
the associated potential conflict between two users who open a new record at
the same time? Or...is my plan to split the table less work & more reliable?

What I really can't figure out is how the table, as it was sent to me, was
incrementing the AutoNumber starting at 15, but there were EXISTING entries
for numbers on both sides of that!

 

[Vincent Johns]

[...]

I'm a bit concerned here about the structure of the current (combined)
data. Are its relationships with other Tables (via the [Field1] and
[Field2] keys) still intact, as far as you know?

This looks like a situation that it would be good to wriggle out of
ASAP, for example by creating a NEW Autonumber field (that can later
become, by itself, the primary key for your Table) and by CAREFULLY
linking the linked Tables using the new key. For example, in a linked
Table (and after making a backup copy of your database to use in case
you accidentally erase something vital), you could add a key field
(Number type) and write an Update Query to set that field's value in
each record to match the new Autonumber value in the goofy Table. Do
this for all Tables linked to the goofy one. Then re-link each Table
using the new fields, and remove the old links.

Having done all that, the relationships should be in good shape (no
orphan records in the other Tables, for example). You can keep the old
[Field1] and [Field2] values if they contain useful data, or delete them
if not. My guess is that you'd want to keep [Field2] and delete [Field1].

[...]

Well, an autonumber is just an autonumber: it functions the same in all
cases. It produces a new number for every record created; usually with a
value of one greater than the last one generated. When a table is
compacted it resets to one greater than the largest value in the table.

Hope that helps

Tim F

And an Autonumber doesn't take lots of space, only about 4 bytes per record.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Brian wrote:

[...]

One point to clarify, however. I don't think this is just about convenience,
but I can certainly stand to be corrected. Field1 is a base contract number,
and Field2 is the type of contract (there are several types), so that a group
of contracts is tied together by their master number. The master contract
number, however, has no other function.

Woopsie... you need to ignore something I said in a previous message. I
guess you do need to hold onto the [Field1] values. But could you give
your friend a hint that some more suggestive name, such as
[BaseContractNumber], would be more informative than calling it [Field1]?

We wanted Field1 to be AutoNumbered
to avoid conflicts between users concurrently creating new master contracts.

It should work as long as everyone goes through that one Table to create
new records. But that's apparently not what happened.

Now that there are duplicates in the list, have you been able to resolve
those, such as by giving new contract numbers to some of the records
that share one [Field1] number?

The existing table needs to have unique Field1/Field2 combinations; thus the
multi-key (not sure if that's the right term for a PK involving multiple
fields). I think I can see that the original developer was attempting to kill
two birds with one stone by:

1. Having an AutoNumber to ensure that each new record got a new base
contract number, but
2. Allow that base number to be used once with each contract type.

You were talking earlier about splitting the Table. Maybe this would be
a valid reason for doing that, assuming that the contracts sharing a
base contract number are more closely related (in the real world) than
those that don't. You could have a [BaseContracts] Table to which are
linked records in a [ContractPhase] Table that identify the contracts of
various types that share that base contract number. This assumes, as
Tim Ferguson said, that they really do have something in common.

One test that I would apply is to try to think of anything, besides the
number, that should be stored in the [BaseContracts] Table. If you
can't think of any other fields that would make sense there, then don't
split the Table.

As you can see, this is not workable. Should I just drop the AutoNumber &
manually increment Field1 using a DMax(Fields1)+1 on the current table, with
the associated potential conflict between two users who open a new record at
the same time? Or...is my plan to split the table less work & more reliable?

If you just need unique numbers, you could do that, or you could
construct a number based on today's date and time; there are many
possibilities.

[...]

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[George Nicholson]

Sounds like the same effect could be achieved by creating a "Master
Contract" table where Autonumbers were unique. Then use your existing table
as a "Sub Contract" table that gets its Contract# from the Master. In the
Subcontract, Autonumber would get turned off but the unique Master# + Type
combination keeps its Unique index).

HTH,

 

[Brian]

As I posted in response to Tim's post, I was just called in to consult on the
development process, but late in the process - after it was mostly built. I
am trying not to completely tear down & rebuild the application, but I may
have to go there, at least concerning this table & its relationships.

I immediately took this one, though, as an original design flaw. I just
needed a sanity check with some other designers (thus my original post) to
make sure that there wasn't some methodology that I had missed in my Access
development experience before I went back to the client to insist on some
structural table redesign.

So far, the responses completely confirm my conclusion, and your concerns &
methods are exactly what I had in mind. Thankfully, there's no live data
yet, though, which simplifies the data integrity part immeasurable.

[...]

I'm a bit concerned here about the structure of the current (combined)
data. Are its relationships with other Tables (via the [Field1] and
[Field2] keys) still intact, as far as you know?

This looks like a situation that it would be good to wriggle out of
ASAP, for example by creating a NEW Autonumber field (that can later
become, by itself, the primary key for your Table) and by CAREFULLY
linking the linked Tables using the new key. For example, in a linked
Table (and after making a backup copy of your database to use in case
you accidentally erase something vital), you could add a key field
(Number type) and write an Update Query to set that field's value in
each record to match the new Autonumber value in the goofy Table. Do
this for all Tables linked to the goofy one. Then re-link each Table
using the new fields, and remove the old links.

Having done all that, the relationships should be in good shape (no
orphan records in the other Tables, for example). You can keep the old
[Field1] and [Field2] values if they contain useful data, or delete them
if not. My guess is that you'd want to keep [Field2] and delete [Field1].

[...]

Well, an autonumber is just an autonumber: it functions the same in all
cases. It produces a new number for every record created; usually with a
value of one greater than the last one generated. When a table is
compacted it resets to one greater than the largest value in the table.

Hope that helps

Tim F

And an Autonumber doesn't take lots of space, only about 4 bytes per record.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Brian]

Yes, it's that "lacking in imagination" part that I was afraid I might be
missing also. After the responses so far, I'm pretty sure it's not a lack of
imaginaton, but just regular old design consistency...

This DB is, in fact, a brand new one where I was called in casually to
provide some advice very late in the development process, so this was a
fundamental design issue. I don't think the user appended from outside, but
is it even possible to force a value into an AutoNumber field like that? I
thought the AutoNumber type required Access to assign the numbers, even when
appending.

Thanks.

 

[Brian]

Exactly my plan (I was just overly verbose in the explanation in my post -
Field1 as an AutoNumber PK in Table1, acting as FK to Field1 in Table2, where
it is part of multi-field PK with Field2). Again, I was just trying to make
sure my client hadn't discovered a clever way around all of this before I
started redesigning tables.

Thanks.

 

[Brian]

Brian wrote:

[...]

One point to clarify, however. I don't think this is just about convenience,
but I can certainly stand to be corrected. Field1 is a base contract number,
and Field2 is the type of contract (there are several types), so that a group
of contracts is tied together by their master number. The master contract
number, however, has no other function.

Woopsie... you need to ignore something I said in a previous message. I
guess you do need to hold onto the [Field1] values. But could you give
your friend a hint that some more suggestive name, such as
[BaseContractNumber], would be more informative than calling it [Field1]?

I was just using Field1/Field2 on the forum to simlify the example; they are
actually called contract_no and doc_no.

We wanted Field1 to be AutoNumbered
to avoid conflicts between users concurrently creating new master contracts.

It should work as long as everyone goes through that one Table to create
new records. But that's apparently not what happened.

Now that there are duplicates in the list, have you been able to resolve
those, such as by giving new contract numbers to some of the records
that share one [Field1] number?

No live data yet, so no concerns there...

The existing table needs to have unique Field1/Field2 combinations; thus the
multi-key (not sure if that's the right term for a PK involving multiple
fields). I think I can see that the original developer was attempting to kill
two birds with one stone by:

1. Having an AutoNumber to ensure that each new record got a new base
contract number, but
2. Allow that base number to be used once with each contract type.

You were talking earlier about splitting the Table. Maybe this would be
a valid reason for doing that, assuming that the contracts sharing a
base contract number are more closely related (in the real world) than
those that don't. You could have a [BaseContracts] Table to which are
linked records in a [ContractPhase] Table that identify the contracts of
various types that share that base contract number. This assumes, as
Tim Ferguson said, that they really do have something in common.

One test that I would apply is to try to think of anything, besides the
number, that should be stored in the [BaseContracts] Table. If you
can't think of any other fields that would make sense there, then don't
split the Table.

I just failed that test, but I still think I need to split. I just need a
unique number, and it has no other relevant information. I wanted AutoNumber
to ensure unique numbers while at the same time avoiding the issue of two
users getting the same number at the same time from a DMax in VBA. The unique
number becomes part of the contract number that is seen by the end user AND
the customer. I'm not sure a timestamp would look so good as a contract
number on a contract or invoice...

As you can see, this is not workable. Should I just drop the AutoNumber &
manually increment Field1 using a DMax(Fields1)+1 on the current table, with
the associated potential conflict between two users who open a new record at
the same time? Or...is my plan to split the table less work & more reliable?

If you just need unique numbers, you could do that, or you could
construct a number based on today's date and time; there are many
possibilities.

[...]

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Brian wrote:

[...]

So far, the responses completely confirm my conclusion, and your concerns &
methods are exactly what I had in mind. Thankfully, there's no live data
yet, though, which simplifies the data integrity part immeasurable.

OK, I'm happy to hear that; you won't have to do anything like what I
suggested.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tim Ferguson]

I was just using Field1/Field2 on the forum to simlify the example;
they are actually called contract_no and doc_no.

Thanks for this: it may be my problem but I always find it easier to talk
cases rather than abstracts.

One test that I would apply is to try to think of anything, besides
the number, that should be stored in the [BaseContracts] Table. If
you can't think of any other fields that would make sense there, then
don't split the Table.

I just failed that test, but I still think I need to split.

I am not doubting you, but I find it very slightly odd to have a Contract
without any attributes: FirstParty? SecondParty? SignedBy? SignedDate?
etc? If you really don't have any fields that are dependent on the
Contract_No without the Doc_No then there is no benefit in splitting the
Contract_No into a one-column table. Only hard work!

Having read through the other responses, I don't have a lot different to
add. One question though: about the numbering requirements for the
Doc_No. Are your users expecting Contract 57 to have Documents 1,2,3 and
4? Or will they be happy with 209(57), 228(57) and 313(57) (if you see
what I mean)? If it's the former, then you have no option (using Jet)
other than roll-your-own numbering of one kind or another, using DMax()+1
or something similar. As other posts point out, this means you have to
prevent or trap attempts at creating records without going through the
coded forms. If the users can put up with Doc numbers looking random,
then using an automatic Autonumber is fine and can be safe. Strictly
speaking, it's redundant to make the PK on (Contract_No, Doc_No) since
the Doc_no would be unique anyway, but there is no harm in it.

I
wanted AutoNumber to ensure unique numbers while at the same time
avoiding the issue of two users getting the same number at the same
time from a DMax in VBA. The unique number becomes part of the
contract number that is seen by the end user AND the customer. I'm not
sure a timestamp would look so good as a contract number on a contract
or invoice...

Fair enough: there are other ways of getting unique serial numbers that
are safe in multiuser situations. What I tend to do is:

1) guess a new number using DMax()+1
2) construct an SQL INSERT statement using it
3) attempt the INSERT
4) if the insert worked, then use the record you just created
5) if the insert did not work, increment the number and go
back to (3)

I can probably dig out the code if you need it but you can likely write
it yourself anyway.

Hope it works out
All the best


Tim F