Question Data Integrity / Sequential Numbering

[irish634]

This is probably a pretty basic question, but I figure I am making it more difficult than it needs to be by overthinking it.

I have an equipment database. I have a table that lists all equipment manufacturers. What I am trying to figure out is how to assign a MFR ID which is sequential to the first letter of the MFR Name.

Example:
Acme Corporation = "A001"
Ajax Incorporated = "A002"
Arrow Corporation = "A003"
Borax Company = "B001"
Etc.

I have no problem counting the # of A's and assigning the next sequential number. Where my concern lies is if/when a MFR may get deleted from the system, my numbering schema will be off. Ex.... If Ajax gets deleted, my count then becomes 2 and the next sequential ID will be "3" causing a duplicate.

I know the simple solution is not to allow deletions, but face it, s#!t happens.

What I am wondering is how are others handling this type of thing? Am I over thinking this? Another way to do it?

Thanks,
Craig

 

[plog]

I think you are both over and underthinking this.

First, why? Why do you need this id number? Is it to be used externally from the database? When people talk in meetings will they reference companies by this id? What is this numbers purpose?

Assuming you have a good reason for it, here's answers to your technical questions:

1. Deleting data should be avoided. Instead you have an 'Inactive' field or something similar where you can designate a company as no longer in use. You still retain the history in case you need it, that way in 4 years you can come back and say Z0011 was company ZZZ and we did this with them. You actually delete that record and you have lost your history.

2. You wouldn't COUNT the number of id numbers, you would find the MAX value of an id and add 1 to it. That way even if one gets deleted, you can assign the next highest number and not generate a duplicate.

Again, though, why?

 

[AccessBlaster]

.. but I figure I am making it more difficult than it needs to be by overthinking it.


Example:
Acme Corporation = "A001"
Ajax Incorporated = "A002"
Arrow Corporation = "A003"
Borax Company = "B001"
Etc.

What I am wondering is how are others handling this type of thing? Am I over thinking this? Another way to do it?

Thanks,
Craig

If I needed a "Manufacture" table or Vendor table, I would let Access handle it by setting a primary key ID within that table.

tblManufacture
___________
ManufactureID
Manufacture
Etc.

 

[jdraw]

Further to comments by plog and AccessBlaster, I have to ask Why do you want the leading character of the manufacturer in such a code? Have you considered companies like Acme, Armstrong, Apple, AArdvark... they all start with "A" --now what?

Relational database is built on some pretty simple concepts and one of those is one fact, one field. Data storage is quite cheap, so making unnecessary codes to reduce fields is not an issue. Similarly, many concocted coding schemes, fall apart under certain circumstances ( eg 2 or more companies with names starting with same character).

Better to define the attributes/fields of your entities/tables based on your business terms.

Before getting into the nuts and bolts of a code to represent Manufacturer or Vendor or whatever, I recommend you write a 5-8 line overview of your business in plain English. Start at the 30,000 ft level and gradually add detail until you have described a typical day in the life of your business.

What is the issue you foresee if manufacturer ids or part numbers are not consecutive numbers? Database management systems are concerned with unique identification of rows (records) in tables. So whether the numbers are 1,2,3 or 5,-7,234 doesn't matter to the underlying DBMS.

Good luck.

 

[gemma-the-husky]

I think this sort of code is often seen. You might get problems when ACME Corp is taken over by Beta Corp, as your account number is still A001. You will most likely end up gravitating to a system where users can search by Account Name, the Account Number is a mere detail, and you don't expect users to know account numbers.

The real thing is that it should not be an issue if a record gets deleted. say you delete Account A003, so you now have

A001
A002
A004

even if A003 gets deleted. the next account number issued needs to be A005. Keep the account numbers issued in a record book somewhere, or an excel file. Seriously. Manual records of some things are indispensible.


---
you avoid duplicates by instead of counting "A"s - you find the highest "A", and add one to it. (dmax is the domain function). It becomes a pain with strings, as you need to split the alpha part from the number part to be able to increment the number. Doable, but a pain. It also requires a string to be a consistent length, as once you get past A999 you have a problem, since A999 is a bigger value than A1000 as far as a computer goes.

Hence the many suggestions to use only numeric.

 

[irish634]

First, why? Why do you need this id number? Is it to be used externally from the database? When people talk in meetings will they reference companies by this id? What is this numbers purpose?

It's used elsewhere and in barcodes.

1. Deleting data should be avoided. Instead you have an 'Inactive' field or something similar where you can designate a company as no longer in use. You still retain the history in case you need it, that way in 4 years you can come back and say Z0011 was company ZZZ and we did this with them. You actually delete that record and you have lost your history.

Agreed. I generally don't allow deletions, but like I said sh.. stuff happens.

2. You wouldn't COUNT the number of id numbers, you would find the MAX value of an id and add 1 to it. That way even if one gets deleted, you can assign the next highest number and not generate a duplicate.

So yep, over thinking... Max would certainly take care of that. Just a little more parsing to code.

Further to comments by plog and AccessBlaster, I have to ask Why do you want the leading character of the manufacturer in such a code? Have you considered companies like Acme, Armstrong, Apple, AArdvark... they all start with "A" --now what?

Explained in the original post. I have several "A's" - I handle them as described.

 

[jdraw]

Craig,

If you use this scheme in other parts of your organization and it is under control, then go for it. I mis-read your use of A, but I have seen many people who think they must concoct code to save space-- often a very bad and short sighted idea. I do agree with the what happens when Beta Inc buys Acme -- does Beta take over the A0012, or do you create B1300 and now deal with both A0012 and B1300 to represent that Company?
And then there is what is Zulu Productions buys/takes over Beta ???

It's a different story if you starting a design, or refactoring something you have in operation. Things already in operation can be difficult to change --people , procedures...

 

[The_Doc_Man]

I'll go the other way around this mulberry bush.

If you are trying to normalize your database, you NEVER allow deletions of records (though they may become inactive) because that is the point of normalization. When you have unnormalized data and allow a deletion, you have exceedingly good odds of losing data for something that became idle temporarily. Therefore, you really DO care if you deleted record A0003 because now if you ever have to reverse that decision, you had better have really good records. Relational integrity says that if you have historical records that relate to the vendor (i.e. a REAL relationship, probably many somethings to one vendor), then you COULD not delete that record anyway.

 

[gemma-the-husky]

in point of fact, going on from what The Doc Man just said, If you set up RI on your tables, and do not allow cascading deletes, you would not be able to delete the base account, even if you wished.