Re: Storing of Serial Number

Andrew Hamm wrote: > nobody wrote: > >>Well sort of... > > > not even... > > >>Suppose you wanted to create an order entry where you knew the order >>number at the start of the order... (And yes, there are reasons for >>this...) > > > yes - you might need to store the key in related tables. Still, this > practice will not work. If you are writing an order entry system, it's a > certainty that two or more operators will start an order and overlap the > data entry. So if operator A and operator B both "peek" and find that 200 > is the next serial, what happens to your orders? You are happy with two > orders in the system keyed with the value 200? If you at least have unique > indexes on the key, the 2nd operator who tries to commit will crash, so > your data is safe. > > Don't start to entertain ideas to have tables for storage of the allocated > key, with all the coding wrapped around that. It's a joke, and there are > simpler solutions anyway. > True, and thats why I said sort of. You could lock the table ... ;-) But you're right. Its not a best practice and the fact that we're even talking about it, some maroon would actually try it out... > >>But then again, I wouldn't recommend a serial number, but possibly a >>stored procedure or some code within the app to generate it. Like >>those 6 - 8 character strings used by reservation systems as locator >>codes. > > > This is the limitation of serials - the inability to use it's value until > the row has been inserted. You might like to investigate the new-fangled > "sequences" added to the engines recently. It appears to be a direct clone > of Oracle sequences. Sequences have their own weaknesses, but fortunately > the have different ones, so there's more chance of a decent solution these > days. > Naw. The weaknesses in Oracle's sequences is a bad solution.... > There are a few solutions which stick to using serials. > > 1) since a serial is not allocated until you INSERT, insert a new row > EARLY in the transaction! Unfortunately most of your fields will have no > value, and the row will probably fail a lot of NOT NULL and other > constraints. The solution to this is to modify your record to have some > field which indicates the state of the record, and take this value into > account in your CHECK constraints (and replace all NOT NULL with a > suitable smarter CHECK constraint) > Bad idea. Very inefficient, and as you point out if you have constraints, you'll have a lot more headaches. > > Beware that any of these techniques may result in a "lost" number if the > operator decides to cancel the data entry before committing. Therefore do > NOT let an accountant or a CEO see these numbers on a print out! They will > ask you where the missing orders are. You will say "they are not missing, > they were cancelled data entry". The accountant or CEO will say "Yes, but > how do I know it's not a missing order?" and you might reply again, but > you'll go round in circles until you realise the accountant or CEO is > actually right for a change. > > The new sequences are probably the better bet for complex situations. What you'd want to do is to create a simple Table A_seq (Sequence Table for Table A) it would contain a character field (Remember you'd want to use a mix between Alpha and Numeric for your id, and ignore "dirty words" (Like SEX112...) from appearing. It would also have a second column for status. (Open, entry cancelled, Closed, etc...) When you open the order entry (panel, screen, window, jsp page), you get the next in the sequence (probably from a stored procedure that locks the table, gets the last entry, creates the new sequence id, inserts it in to the table, then unlocks the table and returns the sequence number to be used. Note, this should be a very fast action so the lock shouldn't be too inefficient. This way you can track the state of orders that are "lost". Customer did not pursue, Created in error... etc.. Works well if you want to monitor the number of "drops" and which DSR isn't closing deals. (DSP = Direct Sales Person, which is a polite way of calling someone a telesales entry clerk) But hey, its not like I've done this before. ;-) And yeah, Oracle blows.