Results 1 to 6 of 6

Thread: 1:n => 1:1

  1. #1
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Unanswered: 1:n => 1:1

    Hi everybody,

    I developed a datawarehouse with salesdata.
    Structure
    Header - Position with e.g. $ and QTY.

    So far so good.
    Now, my customer wants to see "keywords" connected to the Position.
    This is a 1:n (one to many) relation and the keywords from a separated table
    are connected with HeaderNr, PositionNr.

    There is a maximum of 200 keywords per Position but normaly
    it would be 3 to 10 keywords.

    I' searching for a script/procedure which
    inserts keywords semicolon separated into one(!) varchar field.

    Any ideas about that?

    best regards

    Michael

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't understand why you want to denormalise your data?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Displaying the keywords as a comma or semicolon delimited list is fine, but storing them that way is a bad idea. As gvee pointed out in his post, doing that violates first normal form.

    I'd create a linking table. That would allow you to address this problem now, and future requirements too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Sep 2003
    Location
    Nuernberg, Germany
    Posts
    36

    Normalize or denormalize, that's the question

    Of course this issue will violate normalisation.

    But....

    what if you look for turnover, quantity, etc in analysis cube
    and would like to have additional information about stored keywords
    because your product is a web advertisment.
    A join would not help because that will double, triple, etc. your measures.

    Ok,
    I analysed the data and there are some orders with more than 6000 keywords.
    Not a good idea for one text field.

    Infact it showed up that it is ok for the customer to get dwh facttable plus
    keyword table so he can do his own views.

    Nevertheless I'm interested in a technique to
    bring - let's say - max 10 fields semicolon separated into one new field.

    So I'm still curios about that :-)

    best regards

    Michael

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Most people who try this approach run into problems trying to
    • insert new keywords (while making sure all keywords are distinct).
    • deleting keywords
    • updating existing keywords


    For performance reasons you will not likely want to update every record that has the keyword or not.
    Last edited by MCrowley; 01-16-14 at 14:03. Reason: Fixing bbcode issues

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Bring in 10?
    Code:
    ; WITH keywords AS (
      SELECT order_number
           , keyword
           , Row_Number() OVER (PARTITION BY order_number ORDER BY keyword) As seq
      FROM   your_table
    )
    SELECT order_number
         , Max(CASE WHEN seq =  1 THEN keyword END) As keyword_1
         , Max(CASE WHEN seq =  2 THEN keyword END) As keyword_2
         , ...
         , Max(CASE WHEN seq = 10 THEN keyword END) As keyword_10
    FROM   keywords
    WHERE  seq <= 10
    GROUP
        BY order_number
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •