I am new to this forum so this will be my first posting. I have read a lot and i gather there is two groups, one favouring surrogate keys and another that sees them as a necessary evil. I tend to agree that you should use natural keys as long as possible, only when you don't have one let the database or you yourself generate it programatically. But i have a few questions regarding naming convention and common practise.

1. Would you say that Id as a suffix is a naming convention for a identity column (auto generated by the database)

2. Have you ever had a solution where you had you own number series table, where each row represented some type of number series for example order numbers, invoice numbers etc?

For example:

Table NumberSeries

Id (user entered)
Name
Type
Start
Current
End

3. If you had some column used as primary key and you generated it yourself programmatically with a set of business rules, would you still call it a Id?
For example, if I generate something like this:

ADD12344GQ = orderNumber or orderId

and if you would have a new customer
Company offical name : The Great Company Ltd
and you either:

- generated key programatically
- or entered by use
would you still call it CustomerId or CustomerNumber?

What is your experience regarding for example customer id:s? Do you use both a surrogate key and a natural key? I guess you could you things as tax registration number, organization number or some other entity to uniquely identity a customer.

I have taken customer as an example but i could apply to anything. Supplier, Vendor etc.

This is just general thoughts and it might be rambling a bit but i would appreciate your input.

1. Yes, I always end identity columns in ID, and have seen it several places.
2. No, I have never done that.
3. I wouldn't call a character column orderNumber, since it isn't a number. I would either call it orderID or orderKey. I really prefer ID to be computer generated, user entered would keys would end in Key.

But all of that is my opinion. The most important thing is to pick a standard and stick to it.

Thanks Mark, Yeah i guess it as you say. The reason i was wondering is that i have worked for a long time with a legacy system. I haven't really seen a problem with the data model but as i have seen now you solve things differently.
Another example from the legacy system.

Customer Order Table

OrderNumber (PK) generated from a number series table
OrderType (FK)
...
And other fields

Customer Order Types
OrderType (PK) user entered
Name
Description

A solution now days i guess is this one,

Customer Order Table

OrderNumber (PK) identity column
OrderTypeId (FK)
...
And other fields

Customer Order Types
OrderTypeId (PK) identity column
Name (this is the identity you would see as a user)
Description
.....

But lets say you have divisions within a company that are separated from each other. That would mean that you could have a composite key
with Division and OrderNumber as a key inte the Customer Order Table.

Customer Order Table

Division (PK/FK) Here division would be both a foreign key and Primary Key
OrderNumber (PK) identity column
OrderTypeId (FK)
...

Does this seem like a odd design?

I rather like both the number series solution and the legacy way. When i have used identity keys, it became messy generated keys and migrating, and renumbering and such. But maybe it's just me who is inexperienced.

I actually only see a real need for identity columns, only when you really don't have a natural key like for example for different kinds of logs where the closest to a natural key would be a timestamp. But please give me more examples of when you really need a identity column.