Is the usage of identity insert good with metadatatables.

Question

Hi,

I have several tables within my database that contains nothing but "metadata". For example we have different grouptypes, contentItemTypes, languages, ect.

the problem is, if you use automatic numbering then it is possible that you create gaps. The id's are used within our code so, the number is very important.

Now I wonder if it isn't better not to use autonumbering within these tables?

Now we have create the row in the database first, before we can write our code. And in my opinion this should not be the case.

What do you guys think?

Answer 1

I would use an identity column as you suggest to be your primary key(surrogate key) and then assign your you candidate key (identifier from your system) to be a standard column but apply a unique constraint to it. This way you can ensure you do not insert duplicate records.

Make sense?

Answer 2

Well, if those numbers are important to you because they'll be in code, I would probably not use an IDENTITY.

Instead, just make sure you use a INT column and make it the primary key - in that case, you will have to provide the ID's yourself, adn they'll have to be unique.

Marc

Answer 3

If the numbers are hardcoded in your code, don't use identity fields. Hardcode them in the database as well as they'll be less prone to changing because someone scripted a database badly.

Answer 4

I would use an identity column as the primary key also just for simplicity sake of inserting the records into the database, but then use a column for type of metadata, I call mine LookUpType(int), as well as columns for LookUpId (int value in code) or value in select lists, LookUpName(string), and if those values require additional settings so to speak use extra columns. I personally use two extras, LookUpKey for hierarchical relations, and LookUpValue for abbreviations or alternate values of LookUpName.

Answer 5

if these are FK tables used just to expand codes into a description or contain other attributes, then I would NOT use an IDENTITY. Identity are good for ever inserting user data, metadata tables are usually static. When you deploy a update to your code, you don't want to be suprised and have an IDENTITY value different than you expect.

For example, you add a new value to the "Languages" table, you expect the ID will be 6, but for some reason (development is out of sync, another person has not implemented their next language type, etc) the next identity you get is different say 7. You then insert or convert a bunch of rows having using Language ID=6 which all fail becuase it does not exist (it is 7 iin the metadata table). Worse yet, they all actuall insert or update because the value 6 you thought was yours was already in the medadata table and you now have a mix of two items sharing the same 6 value, and your new 7 value is left unused.

I would pick the proper data type based on how many codes you need, how often you will need to look at it (CHARs are nice to look at for a few values, helps with memory).

for example, if you only have a few groups, and you'll often look at the raw data, then a char(1) may be good:

GroupTypes table
-----------------
GroupType            char(1)    --'M'=manufacturing, 'P'=purchasing, 'S'=sales
GroupTypeDescription varchar(100)

however, if there are many different values, then some form of an int (tinyint, smallint, int, bigint) may do it:

EmailTypes table
----------------
EmailType            smallint    --2 bytes, up to 32k different positive values
EmailTypeDescription varchar(100)