Re: Storing of Serial Number

Andrew Hamm wrote: > Art S. Kagel wrote: > >>You can view it through the SMI/sysmaster interface: > > But never USE this in application code Guido! Never let application code > do this. If you are making application code that needs to know "the next > value of a serial" then you are going down a very slippery path, and you > will fall over very soon. > > It's only sensible to use this kind of thing in administration activities, > typically when transporting data where there might have been row deletions > in the table, yet you want to maintain the current max value when you move > the data to a new table or engine. > > Well put Andrew. There is NO defensible<SP?> reason to peak at the next serial value in application code. The practice is a recipe for disaster. Art S. Kagel

Art S. Kagel wrote: > Andrew Hamm wrote: > > Well put Andrew. There is NO defensible<SP?> reason to peak at the next > serial value in application code. The practice is a recipe for disaster. > > Art S. Kagel Well sort of... 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...) 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.

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. > 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. 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) Since we are talking order entry, it's a good example of a complex piece of data, and it's almost certainly already got a status field containing values like "PND", "BKO", "CAN", "DLV", "PST" etc. Add a new legal status "NEW". Change all the constraints to read: col3 check (status = "NEW" or col3 is not null), col5 check (status = "NEW" or (col3 is not null and col3 > 0)), ..... now you can insert a row with (0, "NEW") for the serial and status fields, and you have the serial number. Some problems with this solution is if orders are committed with value "NEW". You probably should make sure the programs do not actually commit a "NEW" record, if you can avoid it. Otherwise you might need to have daily or weekly procedures looking for lost "NEW" orders and perhaps throw them away or raise an alert. 2nd solution is to have a separate table which allocates numbers. You need one row per number stream, eg one each for "AP", "AR", "GL", "OE" etc. You will need to increment, fetch and commit the field quite quickly, otherwise you will have locking contention and a bottleneck. I have seen systems which get bottlenecked even with quick allocate and commits, so do your research very carefully. This technique is like a manual SEQUENCE. 3rd solution is to have a little table with only a serial column in it. When you want a number you insert 0, gather the value allocated, and then delete the row again. This is important because you don't want this stupid little table growing endlessly. However you must delete a little bit carefully since you can hit a locking problem. It's actually better to delete using the rowid, because that means you need no index, and there will be no .... other problems from locking. This technique is like a manual SEQUENCE. This technique has a problem when you export your database. Ideally this table will have no rows. Therefore if you export, the value of the last serial is lost. Bummer. You could use Art's original answer to find the last serial and make sure you manually insert that value before the export and then remove it again on import. 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.

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.