The more I read your book, the more I discover precious gems hidden in it.
I'm glad you're finding it useful! (Post a review when you have a chance.)
...The format is yyyynnnn. For 2014, it would become 20140001, 20140002 etc. (New enrollment is only around 1000 students per year)
My question is, do I still need to create any AutoNumber StudentID field for this table?
Would I just go ahead and set the StudentRefNo as Primary Key and not require the AutoNumber StudentID field at all?
Good question. Basically you're generating the Student IDs yourself. The auto-generated StudentID is used as a primary key to guarantee uniqueness (since first and last name isn't quite good enough). Because you're StudentRefNo value is unique, you don't really need both. So yes, you can drop the StudentID field.
In fact, I would probably just name your field StudentID and drop the notion of auto-numbering. The name StudentID will probably make more intuitive sense than StudentRefNo.
Two quick notes, however.
(1) I'm not sure how you're generating the numbers, but it can be a hassle if two people try to grab the next number in the sequence at the same time. It may not happen often so it can lead to a rare bug that's hard to reproduce. Some methods to handle this are:
- Catch the exception and try again until you succeed. This isn't very elegant but if it doesn't happen often it shouldn't waste too much time.
- Create a record somewhere, like a Parameters table with fields ParameterName and Value. Make a parameter named LastUsedId and initially set it to 0. To create a new ID, lock the record, get the value, increment the value, and then unlock the record. Because you lock the record before you get the value, you know that no one else is trying to do the same thing at the same time.
- Make this an auto-number field and initialize it to 20140000 at the start of the year 2014. Then the database's auto-number feature will do this for you automatically. You'll need to remember to reinitialize the start value each year (e.g. 20150000 for next year), but you're going to need to update the start value every year anyway.
If you're using an Access database, I think you can set the initial value like this:
ALTER TABLE Students
ALTER COLUMN StudentId AUTOINCREMENT(20140000, 1)
Here 20140000 is the initial value you want to use and 1 is the amount by which to increment the counter each time you generate a number.
(I'll post an example on my C# Helper
web site in a few days.)
- Give each user a slice of values and have them use only those. For example, user 1 assigns values 20141xxx, user 2 assigns values 20142xxx, etc. Then you know there's no contention. (It does limit the number of values that each user can assign, however. You could add another digit if necessary.)
(2) You could just make the StudentID (or StudentRefNo if you prefer) be a composite field consisting of the year plus an auto-number value. Then you get the behavior you want pretty much for free. I think the only downside is that you need to combine the two values to get the StudentID.
I hope that helps. Let me which approach you take and how it works out.