Building a XREF table RRS feed

  • Question

  • I am in the middle of building a cross reference table for my db.  The issue is that I have a table of items I call AN and each row in that table can have several aliases.  I have a unique key ANID with every row and I reference that key in other tables.  Some ANs can have the same alias as another ANs.  So I want to build a cross reference (XREF) table that has the Alias as the key and will hold the ANIds to so I can quickly reference back to the various ANs so I can make a choice of which AN I meant when I use the alias. 

    An example of this is if the AN was Robert Bosch and the Alias was Bob or Rob or Bobby.  Now I may have many Bobs in the Alias field of my AN table so I don't want to search my entire AN table to find them all if I enter Bob as the alias in a search. 

    The only thing I can think of for my XREF table is to set up a nvarchar column (call it ANameID) use that as a csv string where I would translate the ANIDs from integer to characters and then place them into the string separated by commas.  Then to find the possible ANs based on an alias I would then search the XREF table to find the alias and deconstruct the ANameID field back to ANIDs.

    This would require a lot of translation from integers to characters and back again.

    Is there any other simpler way to do this? 

    • Moved by Kalman Toth Saturday, March 21, 2015 3:24 PM Better fit
    Friday, March 6, 2015 9:07 PM


All replies