p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 (http://p2p.wrox.com/forumdisplay.php?f=430)
-   -   Concatenated Surrogate Key replacing AutoNumber Key (http://p2p.wrox.com/showthread.php?t=92865)

frdata May 12th, 2014 12:46 PM

Concatenated Surrogate Key replacing AutoNumber Key
 
Hi Rod,

The more I read your book, the more I discover precious gems hidden in it.

I'm creating a surrogate key, StudentRefNo, in Students table for new students registration. (I already have a composite unique constraint on some other fields to determine uniqueness)

The format is yyyynnnn. For 2014, it would become 20140001, 20140002 etc. (New enrollment is only around 1000 students per year)

The numbering would not necessarily be sequential but guaranted to be unique because of the unique constraint placed on the StudentRefNo field.

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?

Thank you

Rod Stephens May 12th, 2014 04:35 PM

Quote:

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.)

Quote:

...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:

Code:

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.

frdata May 13th, 2014 01:19 AM

What can I say. Brilliant answer!

Your answer is comprehensive and this proves that you're an expert in many aspects of database programming.

Quote : " I'm glad you're finding it useful! (Post a review when you have a chance.)"

That's the least I could do [:)]

Quote : " Let me know which approach you take and how it works out"

Method (1) with record locking seemed more intuitive to me. I would post feedback about its usage from time-to-time.

Quote : "(I'll post an example on my C# Helper web site in a few days.)"

I hope you would also have time to post an example in your vb-helper web site. Since that website is mainly about the old vb6, why dont you make codes available to VB.Net also. I see that you're writing books for many versions of VB.Net [:)]

I already bought your "Stephens' Visual Basic Programming 24-Hour Trainer" over a year now but have not really got time to go through it yet.

Thank you

EDIT :

Come to think of it, method (2) might be better than method (1). It might eliminate problems related to contentions and record locking issues. I'll also give it a try

Rod Stephens May 13th, 2014 11:38 AM

1 Attachment(s)
Quote:

I hope you would also have time to post an example in your vb-helper web site. Since that website is mainly about the old vb6, why dont you make codes available to VB.Net also. I see that you're writing books for many versions of VB.Net [:)]
Unfortunately I haven't had time to maintain the VB Helper site much. That site is probably about 2/3 VB .NET at this point. Unfortunately the VB book market isn't as strong as the C# market so I'm writing mostly C# right now.

I'm attaching an example to this post so you can see how to get a newly inserted auto-number value and how to reset the initial value.

(Note that this can cause problems if you're not careful. In your example it shouldn't, but suppose you reset the initial value to 1. When you then insert a new record, you'll get the value 1. If that value has already been used and if the field requires uniqueness, which a lot of auto-number fields do, then you'll get a duplicate value exception. As I said, this probably won't be a problem for you if you only set it forward each year.)

frdata May 13th, 2014 12:54 PM

The code worked perfectly. Thank you very much for sharing it here.

I have tried to turn off the Primary Key constraint for StudentID and Access allows duplicate values in it using the code you provided.

So, I have a lot of flexibility to design the StudentID[:)]

Rod Stephens May 13th, 2014 04:10 PM

Of course for this application, you can't allow duplicate values for StudentId. You can allow duplicates of the auto-numbered field as long as it's part of a larger primary key, such as year + auto_number.

But it would probably be very confusing to give two students the same ID. [;)]


All times are GMT -4. The time now is 08:29 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.