Autoincrementing hierarchical IDs on SQL Server

Question

Consider this table on SQL Server

wordID    aliasID    value
===========================
0         0          'cat'
1         0          'dog'
2         0          'argh'
2         1          'ugh'

WordID is a id of a word which possibly has aliases. AliasID determines a specific alias to a word.

So above 'argh' and 'ugh' are aliases to each other.

I'd like to be able to insert new words which do not have any aliases in the table without having to query the table for a free wordID value first. Inserting value 'hey' without specifying a wordID or an aliasID, a row looking like this would be created:

wordID    aliasID    value
===========================
3         0          'hey'

Is this possible and how?

Answer 1

There may be some other options (and a sql server expert may be able to shed more light on this), but three options you have are:

1. Define a trigger on insert that updates the values in the table (yuck!)
2. Normalise your tables into separate words and aliases tables each with their own identity column for their ID

3. Restructure your insert statement to be something along the lines of;

   insert into aliases( select <your new alias>, max(aliasId)+1, max(wordId) +1 from aliases)

   for a new word and alias

I understand that neither of these completely answers your question, and all are pretty miserable solutions!...Although I think I favour the last one out of them all

Answer 2

I've had to build a similar thing, and normalization will make your life much easier. Pseudocode:

TABLE Word
    int WordID IDENTITY
    string reference_value /* just for debugging */

TABLE Synonym
    int SynonymID IDENTITY
    int WordID
    string value

Answer 3

It looks like aliasID is only being used to differentiate between records with the same WordID. Usually in cases like this it really doesn't matter that the numbers restart at zero. I would just put an identity column on aliasID and they will be unique within the set of values with the same WordID.

Note that if order is important (as it sometimes is in a situation like this) this solution preserves order.