Member Avatar for zachattack05

I am considering an option for users that will allow them to write their own account numbers or create their own algorythm for creating custom account numbers for customers, but also want an option that will auto-generate them if the user doesn't care by using the identity property in SQL.

I've always heard that if the PK is supposed to mean anything to users (which an account number would), that using the identity feature is a bad idea, but was never really certain on the reason(s) why?

Any suggestions or thoughts on this?

  • 4 Contributors
  • 4 Replies
  • 147 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by reshea1950
Member Avatar for Momerath

I've never heard that, whatever that's worth :)

You'll be violating first normal form if you provide both an identity key and an account key, but sometimes that's just what you have to do. I'd put a trigger on the insert and set account = id if account is null.

Member Avatar for zachattack05

Well what I intended to do was during the setup of the application, ask "do you want to provide account numbers, or auto-generate them?" and if they want to auto-generate them the database would be built setting the identity property to true, and if they want to make up their own, set it to false and let them type their own account number.

I would only have one column, just depends if the identity property is set to true or false when the database is built.

Edited by zachattack05 because: correction
Member Avatar for rotten69

I'd make the account number as a primary key/unique in the database so that way you can pretty much avoid any misleading voilating numbers given by customers because they don't really know what numbers are stored.

Member Avatar for reshea1950

Sometimes for business requirements reasons customer numbers need to be in a particular format or even numbering sequence.
For generating a unique customer number, it sounds that using the Identity number would be OK. At least at first blush.
I am sure that you checked with the business users to see if the number sequence for customers that will be generated will be OK.
I only mention this non-technical issue because we had different business units that demanded a different customer number sequence scheme for each business unit.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.