Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 12th, 2014, 12:46 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
  #2 (permalink)  
Old May 12th, 2014, 04:35 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 13th, 2014)
  #3 (permalink)  
Old May 13th, 2014, 01:19 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Smile

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

Last edited by frdata; May 13th, 2014 at 01:42 AM. Reason: Add something
Reply With Quote
  #4 (permalink)  
Old May 13th, 2014, 11:38 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.)
Attached Files
File Type: zip howto_net_reset_access_autonumber.zip (35.2 KB, 5 views)
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 13th, 2014)
  #5 (permalink)  
Old May 13th, 2014, 12:54 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

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

Last edited by frdata; May 13th, 2014 at 02:29 PM.
Reply With Quote
  #6 (permalink)  
Old May 13th, 2014, 04:10 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
AutoNumber for Primary Key Yusrina Access 7 January 7th, 2009 12:59 AM
Surrogate key ssaranam SQL Server 2005 0 January 10th, 2008 02:52 AM
<bean:message key="PNR.INPUT"/> key has null value warsha_14 Struts 1 November 13th, 2006 06:26 AM
recovering an autonumber primary key Loralee Access 10 October 2nd, 2005 12:54 AM
Autonumber Primary Key JonnyRPI Access 1 June 27th, 2003 10:59 PM



All times are GMT -4. The time now is 09:07 PM.


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