Re: Storing of Serial Number

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.