SQL Server Performance

RE: Variable help or "Re-Design"

Discussion in 'SQL Server 2005 General Developer Questions' started by lmeinke, Dec 8, 2008.

  1. lmeinke New Member

    Hello All,
    I have a large table of items or SKU’s that contains stocking flag by column ([01], [02],[03]…[50]). The name of each column corresponds to an actual stocking location code in another table. The data in these columns is also the same as the column name (i.e. [01], [02]) or it is NULL if the item is not in that location.
    In this same table I have a column [Optimum] that lists the code for the optimal location to pull this item or stock from.
    I need to be able to determine if the value in the [optimum] column has a match in the appropriate stocking location columns. (I need to tell the table to check the appropriate column ([01], [02] etc…) and see if there is a value (anything other than “NULL”) in that column.)
    I am thinking I need to read the [Optimum] column value into a variable and then somehow check that column (and record) for a match. Any better ideas or thoughts on how I might do this?
    1) Before you answer … I am fairly “stuck” with this table design. I would be open to hearing any better design ideas but am somewhat limited in what I can implement.
    2) Please be explicit in any coding suggestions as I am fairly limited J here as well.
    I hope I have explained this well enough, if not please email me back for any clarification necessary.
    Thank you,
    Lonnie M
  2. Adriaan New Member

    It's basic normalization: the stocking columns are repeating values, so they should go into a child table.
    Your gut feeling is telling you all the right things. With the locations 01-50 organized in columns, you can't have an index that will help you find a value on one of those columns.
    You now have MyTable (Item, Detail1, Detail2, [01], [02], [03])
    You should have MyParent (Item, Detail1, Detail2) and MyChild (Item, Location, LocationValue)
    There would not have to be an "Optimum" column: you would query the child table to find the optimum location.

Share This Page