Lookup tables
-
What is the best way to implement multiple descriptions for a single code? For example, say 25 is a code in a table that means "maternal grandmother". I also want the user to be able to select "grandmother, maternal" or "maternal, second degree relative", etc. and have those all map to 25 as the code.
For RI, I need for the code to be unique in the lookup table and want all the possible descriptions in a single list.
Thanks for the help.
Sam
-
I think I would do a normal lookup table and then add another table with the alternative namings of the key. Why do you need to do that?
-
Why? to help the user. In the real world the selection list could be very long and many different ways to list an entry that all mean the same thing and should be coded uniformly.
Sam
-
K... never had that requested to me
. -
Me neither - can't you just give them a longer description? This is building complexity that seems over the top. Are your users particularly (hmmm, what's a nice way of putting it?) difficult?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Well, this is the medical world and that probably explains it. Terminology can be very complex and several different words or phrases can all mean the same thing. To have any hope of meaningful analysis, it's necessary to anticipate that.
Sam
-
The question is though, do you also need to remember which description of the lookup value the user chose? That would have implications on which design to use.
-
How about some XML?
DECLARE @idoc int
DECLARE @doc varchar(1000)
DECLARE @list varchar(100)
DECLARE @xpath varchar(50)
DECLARE @lookupID char(2)
SET @lookupID = '25'
SET @xpath = '/ROOT/Code[@codeID=' + @lookupID + ']/Alternate'
SET @list = ''
SET @doc ='
Hello
World
Welcome
To
SQL Server Central
Joe
'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT @list = @list + COALESCE(Alternate, '') + ','
FROM OPENXML (@idoc, @xpath, 2)
WITH (Alternate varchar(50) 'text()')
EXEC sp_xml_removedocument @idoc
PRINT REPLACE(@list + ',', ',,', '')
The print statement would produce:
Hello,World,Welcome,To,SQL Server Central
Wrap it in a stored proc and away you go. For every given loopup value .. in this case 25 .. you'd get a list of alternate words.
-
Chris: I don't need to remember which description the user selected.
Neil: thanks! But I'm a non-techie and that is way over my head.
-
Which bit?
All of it or just the implementation?
-
Neil,
I am familiar with vba on the frontend. So I don't have an XML editor.
Sam
-
Ok, then the easiest way would probably be to add a table with alternate texts for lookup values as Remi suggested above.
-
Why would he need to use XML for this??
-
I'm still not over this one
. -
I've seen this kind of thing a lot, usually working to convert free form text input fields into the "standard" values. just make two lists, one for the user input and the code you want, the other for the output side of things.
create table Lookups(
Listname varchar (10) not null
, Textvalue varchar (x) not null
, Codevalue int not null
)
insert into lookups ('UserInput','maternal grandmother',25)
insert into lookups ('UserInput','grandmother, maternal',25)
insert into lookups ('UserInput','maternal maternal, second degree relative',25)
insert into lookups ('Display','Maternal Grandmother',25)
then when you are inserting, use the 'UserInput' list and when you are fetching for display, use the 'Display' list.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply