Post reply

split field into numbers and join to table

  • July 15, 2009 at 3:09 am

    #136523

    hi,

    I have a table with fields CategoryID and Category

    Then I have another table that has fields MakeID, Make and CategoryIDs.

    The tables are designed like this so makes can be reused on mulitple categories.

    data is :

    CategoryID Category

    1 PC

    2 Laptop

    3 Printer

    MakeID Make CategoryIDs

    1 Dell 1,2,3

    2 Sony 1,2

    How can I join these tables together ?

    Or is this poor design and I should design my Make table like :

    Make ID Make CategoryID

    1 Dell 1

    1 Dell 2

    1 Dell 3

    any ideas will be apreciated.

    Jamie

  • July 15, 2009 at 4:26 am

    #1024914

    hi,

    you can also create an intermediate table -

    MakeID CategoryID

    1 1

    2 1

    3 1

    4 2

    and join all the three tables in your queries.

  • July 15, 2009 at 4:26 am

    #1024915

    hi,

    you can also create an intermediate table -

    MakeID CategoryID

    1 1

    2 1

    3 1

    4 2

    and join all the three tables in your queries.

  • July 15, 2009 at 4:53 am

    #1024921

    of course !!

    I dont know what I was thinking then.

    thank you.

  • July 19, 2009 at 9:03 pm

    #1026692

    So, Jamie... now you have 3 tables to maintain instead of just 2. The real problem is this table...

    MakeID Make CategoryIDs

    1 Dell 1,2,3

    2 Sony 1,2

    It's not normalized. Tables should never contain CSV's. Are you interested in normalizing the table or at least learning how to use it as if it were normalized instead of making yet another table?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 20, 2009 at 6:27 am

    #1026888

    hi Jeff,

    I am now ok with this, storing the data like 1,2,3 was stupid.

    I have just created a link table , so I do now have 3 tables instead of 2, but this makes more sense to me and isn't the data now normalised ?

    ie, the data is not actually duplicated anywhere , the link table takes care ofthis.

  • July 20, 2009 at 9:16 am

    #1027048

    I was just wondering if you are able to change the tables so that you don't have a CSV column at all. But, if you're happy, I'm happy. Thanks for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 22, 2009 at 3:52 am

    #1028177

    So, Jamie... now you have 3 tables to maintain instead of just 2. The real problem is this table...

    MakeID Make CategoryIDs

    1 Dell 1,2,3

    2 Sony 1,2

    It's not normalized. Tables should never contain CSV's. Are you interested in normalizing the table or at least learning how to use it as if it were normalized instead of making yet another table?

    Hi Jeff,

    I completely missed the point here.

    I want to know how will you normalize above tables keeping the number of tables to just two (getting rid of the link table)?

    As you can see it's the lack of understanding of some basics on my part.

    Please tell me in detail about the alternative that you didn't mention.

    Thanks in advance.

  • July 22, 2009 at 6:05 am

    #1028227

    My humble apologies... looking back at this thread, I was wrong because I misread the intention. I allowed my fervor against having a CSV column in a table to blind me a bit. 3 Tables is the correct way to go on this provided that the CSV column is normalized to a non-CSV column. I thought the original table with the CSV was going to be allowed to stand pat and that would have been the wrong thing to do.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply