J J <mailto:[EMAIL PROTECTED]> on Wednesday, March 10, 2004 4:03 PM said:
> I'd like to keep it in one field if possible since > that's how their database is now and data will be > going back and forth.
[snip]
> So any state with more than one record can be > sequential and not jump around like KY00003, AL00004, > KY00005, etc. > > Make sense?
yeah that makes sense. hmm.. well i'll give you my idea.. but it's very possible there's a better way to do it (than what i'm going to suggest).
ok. one field will be used. it'll be a text (varchar) field. before you do an insert you'll need to find out what's in there ahead of time and then adjust accordingly. i'd select the last record that contained the two letter abbreviation, find out what the number was, increment it, then reconstruct the id and insert the new one.
this of course won't prevent the exact same process from happening at the exact same time. i mean, two sessions could pull the same id and then reconstruct the (new, but) same id.
make sense?
chris.
Yes Chris, that's the right idea. The problem is that this is being done across the 'Net. Ideally you try and get a lock on the row for a given state, fetch the value and increment/update, then release the lock. In a web environment locks are a little tricky - what happens if a connection is lost or times out?
For the original poster: Having said all this, make certain that there is a unique, system-generated, primary key for each table. As these keys *never* have to be seen by the public, don't get tampered with, etc., they can be safely relied on for inter-table relationships. Down the road they will save your bacon.
If the purpose of having separate numbering sequences for each state is to keep track of a count, why bother? Just select for a count on each state. If it's a matter of ego, in that if I have a lower registration number I have higher status, well fill your boots with whatever scheme will work.
Really look at this v. closely. Quite often clients insist on wacko numbering schemes which they are convinced are important, but frequently result only because that's the way it's always been done. Also remember there should be no data encoding within a field - that's why so many columns are possible.
Example, membership numbers like MABT082003BM2, where the first four characters are my initials, the next six the date I joined, and each of the next the colour of my eyes, marital status and number of children, ARE FORBIDDEN. All that information belongs in separate fields. This is what you are tending towards with AL0003 and KY00107. Bad practice.
As I'm Canadian, if I stepped on any toes I'll apologize in advance. <g>
Cheers - Miles
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
