Need to look up descriptions from a list of IDs

Posted on 2012-09-12
Last Modified: 2012-09-13
I have a field in my Customers table called Skills.  The customer could have more than one skill so I store the Skill_IDs as a comma separated list in the Skills field.  I also have a Skills table with Skill_IDs and Skill_Descriptions.  After retrieving the comma separated list of Skill_IDs, I need to display them as the Skill_Descriptions.  What is the best approach for this?

My approach was to spit the list into an array, then dynamically create a SQL statement on the Skills table to retrieve the descriptions whilst navigating through the array of Skill_IDs.  However, I am worried about security using this approach so I really need a way of passing the IDs to an sproc to retrieve the Skill_Descriptions.  Note the comma separated list could be anything - eg. 1,2,3 or 3,4 or 4,5,8.
Question by:Lorna70
    LVL 12

    Accepted Solution

    Personally,  I always find lists of things packed into one field to be a pain!    So if I had control over the data model,  I would implement many-to-many relationships like this via an intersection table e.g. CustomerId, SpecId

    Assuming this is not an option,  I further assume that your security concern is related to SQL injection attacks?

    If so,  why don't you just modify your current approach,  split the list on the client,   verify that each Id in the list is purely numeric,  then query the database either directly or using a procedure that accepts a single Id as a parameter and returns the description.

    Of course, you could implement all of that in the stored procedure and pass in the string.   The important thing is to split the list and verify that each member is a legally formatted Id of a specialty.   That should prevent malicious attack.
    LVL 68

    Assisted Solution

    >> What is the best approach for this? <<

    A Customer_Skills table, the "standard" intersection table when you have a potentially multi-valued column (a column should be a SINGLE value, NOT a list).

    Required here anyway, since you will need additional info about THAT SPECIFIC COMBINATION of values:
        1) when did this specific customer acquire this specific skill?
        2) when did this specific customer LOSE this specific skill? (for example, accreditation or license lost)
        3) restrictions on this skill? / subskill
        4) etc.

    NO WAY you want to store all that in the base Customers table.

    Author Comment

    Great - thanks guys.  We are implementing the first phase as a really simple one but you're both absolutely right - I need to change my data model to cope with future expansion :-)

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    This video discusses moving either the default database or any database to a new volume.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now