Numbering strategies

[Alexander Willey]

Dear Forum,

I am creating a database that has the following tables linked with a one to many relationship. one PROJECT to many TESTS. So, each project has many tests

I want each test to be numbered in such a way that it indicates which project it is from....e.g.

Project ABC: tests would be numbered ABC001, ABC002 etc.

Project XYZ: tests would be numbered XYZ003, XYZ004

I think I am happy to use autonumber for the TESTS table.

I'd be grateful for ideas on how to approach this numbering

Many thanks, Alex

 

[pwbrown]

Well it depends on how you enter your data into the table.
Or have you already got the data in the table?
Unless you need this numbering system for some other reason you could easily use an autonumber in your test table and also include the primary key from the project table in the test table.

 

[Alexander Willey]

Thanks

The database is empty now, but when I get going, the data will enter via forms.

I would ideally like to have the project identifier with the test autonumber all as one. Would concatenation on a query be the best approach?

thanks agin,

Alex

 

[RainLover]

I have found it best to always use Autonumber as the Primary key and link that to a Number field in the other table.

However I never allow my users to see the Primary or Foreign Keys. They are just for me to do my programing.

This should now raise a few questions in your mind, so think about it then post your questions.

 

[gemma-the-husky]

I agree with Rain. I would use this sort of structure

Table Project (ProjectID (autonumber - PK) , ProjecttTtle (your ABC etc), other data relevant to project)

Table Test (TestID (autonumber - PK), ProjectID (FK - corresponds to projectid in project table), TestNo - number field,other data relevant to test)

hope this makes sense

 

[Pat Hartman]

Assigning meaning to keys almost always results in issues if the system lives long enough. Starting with how many numbers to allow for the sequence part since you will need to zero fill the numeric part or you will be disappointed in how the key sorts. That said, there are numerous posts here showing the code necessary to assign your own sequence numbers. The good code uses the Max() function to find the highest value.

However, when assigning your own sequence numbers, you need to be aware of issues that can arise when multiple users are creating records so you will need to code for conflict. That means if user A and user B are both creating a record for the same project at the same time, you could generate a duplicate sequence number. Obviously, some applications have more risk of this happening than others but all should at least recognize the potential error and give the user a second opportunity to save with a new number. The best technique is to not even generate the number until you get to the BeforeUpdate event. This will minimize the window in which duplicates might be generated.

PS - keep the two parts of the generated number as separate columns and add a compound index to make the combination of the two parts unique. You can concatenate the parts for display and for use in combos but always store them separately.

 

[RainLover]

However, when assigning your own sequence numbers, you need to be aware of issues that can arise when multiple users are creating records so you will need to code for conflict. That means if user A and user B are both creating a record for the same project at the same time, you could generate a duplicate sequence number. Obviously, some applications have more risk of this happening than others but all should at least recognize the potential error and give the user a second opportunity to save with a new number. The best technique is to not even generate the number until you get to the BeforeUpdate event. This will minimize the window in which duplicates might be generated.

Pat
The code to prevent duplicates being generated is very complicated. I have never seen any samples on this or any other site. I did write some code but can't remember if I posted it because of its complexities. It is however available in my link to Sky in my signature.

Have you ever written some code. If so I would like to see it.

With regard to duplicate numbers, I can say that I have never come across this problem with AutoNumber. I don't see it a big enough issues to choose a different method of generation.

The only reason I would use DMax is when I want a different Numbering for the Red Flowers as opposed to the Blue Flowers.


EDIT

Pat

Have you ever written some code. If so I would like to see it.

I hope this did not sound rude. I did mean code to do with this subject not code in general.

 

[gemma-the-husky]

Pat
The code to prevent duplicates being generated is very complicated. I have never seen any samples on this or any other site. I did write some code but can't remember if I posted it because of its complexities. It is however available in my link to Sky in my signature.

Have you ever written some code. If so I would like to see it.

With regard to duplicate numbers, I can say that I have never come across this problem with AutoNumber. I don't see it a big enough issues to choose a different method of generation.

The only reason I would use DMax is when I want a different Numbering for the Red Flowers as opposed to the Blue Flowers.

Rain

2 ways to prevent duplicate numbersw being assigned

1. get the number using Dmax at the last possible moment - so in the forms beforeupdate event.

2. get the next number from a separate table, rather than by using a Dmax.

3. autonumbers must manage the seed in a manner similar to 2, above - so will never generate duplicates.

 

[ButtonMoon]

There are various strategies you can use to build custom key generators. In your case, consider using a separate table to persist the state of the latest number generated and then increment that number yourself through procedural code (see also other sequence generation methods like "high-low" and "linear congruential"). Or use your DBMSs in-built sequence generator independently of the table you are populating if that's an option.

The "wrong" way to do it in my opinion is to generate the business/domain key (AKA "natural" key) for your users directly from the table's autoincrementing surrogate key (assuming you have one of those). If you are going to make your surrogate key and natural key values directly dependent on each other then you are very probably better off without the surrogate key at all. A surrogate key by definition is supposed to be independent of, and generally invisible to, database users.

 

[Pat Hartman]

Autonumber keys are assigned as soon as the record is dirtied and the seed is updated so autonumbers don't ever generate duplicates (unless there is corruption but that is a different issue). People who want "meaning" for their autonumbers object to this because if the record doesn't actually get saved, the issued number is lost and can never be reissued.

If you use a table to issue your sequence numbers you will have the same problem as Access. You won't be able to reissue a previously assigned number should the record not be saved. That leaves the Max() method which technically could generate duplicates although it will be rare and if you generate the number at the last possible moment, rather than the earliest as Access does, the potential diminishes even more. That's why I don't actually create a code loop to handle the problem, I just trap the "duplicate" error and tell the user what to do.

 

[ButtonMoon]

Pat,

To generate a sequence without gaps you need to update both the sequence table and the target table in a serializable transaction and then either commit or rollback both updates. This blocks and serializes insert operations of course - but that's the price you must pay for guaranteeing no gaps. There are other methods too in some DBMSs but using a sequence table is the most generic method I know of. For very many applications occasional gaps just aren't a problem though.

 

[RainLover]

Rain

2 ways to prevent duplicate numbersw being assigned

1. get the number using Dmax at the last possible moment - so in the forms beforeupdate event.

2. get the next number from a separate table, rather than by using a Dmax.

3. autonumbers must manage the seed in a manner similar to 2, above - so will never generate duplicates.

Unfortunately your method does not guarantee sequential numbering.

Your point 3. Do you know that Microsoft does this? I doubt it because you can also select random numbers. But how would we know what Microsoft does..

 

[RainLover]

Dear Forum,

I am creating a database that has the following tables linked with a one to many relationship. one PROJECT to many TESTS. So, each project has many tests

I want each test to be numbered in such a way that it indicates which project it is from....e.g.

Project ABC: tests would be numbered ABC001, ABC002 etc.

Project XYZ: tests would be numbered XYZ003, XYZ004

I think I am happy to use autonumber for the TESTS table.

I'd be grateful for ideas on how to approach this numbering

Many thanks, Alex

It would be nice to know where your thinking is. There are quite a few posts but no reply from you as yet.

gemma-the-husky has given some simple instructions as to setting up the Primary Keys and Foreign Keys. I agree totally with him on this.

There is a second set of numbers that you want so that you can have ABC 001 ABC 002 AAA 001 AAA 002 etc.

This is where you utilise the DMax method. It should be treated differently to the Primary and Foreign Keys.

Do you need further help or is this thread closed.