Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2005
    Posts
    93
    Thanks
    5
    Thanked 0 Times in 0 Posts

    How to use lookup table

    Hi,

    I have a look up table with the following fields.

    songid varchar(10)
    songno varchar(20)
    songname varchar(100)
    moviename varchar(100)
    singername varchar(100)
    so the record in this table will look like:

    1 song1 hotel california beatles rick
    2 song2 summer of 69 bon jovi bon jovi

    this table contains 20 songs

    and intermediate table like below.

    id bigint(20)
    song1 varchar(32)
    song1name varchar(100)
    song1played binary(1)
    song1singer varchar(100)
    song1movie varchar(100)
    song2 varchar(32)
    song2name varchar(100)
    song2played binary(1)
    song2singer varchar(100)
    song2movie varchar(100)
    song3 varchar(32)
    song3name varchar(100)
    song3played binary(1)
    song3singer varchar(100)
    song3movie varchar(100)
    song4 varchar(32)
    song4name varchar(100)
    song4played varchar(1)
    song4singer varchar(100)
    song4movie varchar(100)
    song5 varchar(32)
    song5name varchar(100)
    song5played varchar(1)
    song5singer varchar(100)
    song5movie varchar (100)

    the row would look like.

    Here in one row there will be 5 songs.each one is a song number that is in the first lookup table.Now i have to populate other three fields for all the 5 songs depending on the lookup table.(song name,songmovie,singer) these are vailable in lookup table.

    Usin these two table i will have to fill the thitrd table
    with the abive structure.except that its filled with all the corresponding data from lookup table.

    How can i do this someone help me.

    Thanks

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    this will probably help you to design your database:

    http://www.developer.com/db/article.php/3667831

    you are pretty old on cf but seems you never read or want to follow the rules and posting guidelines:

    http://www.codingforums.com/rules.htm
    1.2 - cross-posting:

    http://www.codingforums.com/showthre...715#post804715

    also you don't use tags for code, [ code] and [ /code] tags could make your post readable and you have more many chance for an answer.

    best regards

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,370 Times in 4,335 Posts
    Oesxyl, you are an optimist.

    I don't think he will read and understand that article.

    Raj: Your DB design is all wrong. Which is what Oesxyl was saying, just not in so many words.

    Your so-called "intermediate table" is anything *but* an intermediate table. It is, instead, the worst design you could choose, pretty much.

    A much much better design would be something like this:
    Code:
    Table: Songs   (a lookup table)
        songid int primary key  [no reason to use varchar for id...but you could]
        songno varchar(20)
        songname varchar(100)
        moviename varchar(100)
        singername varchar(100)
    
    Table: People  [or whatever the reason/use of your main table is]
        id int primary key    [no reason to be bigint, int is plenty big enough]
        ... other fields related to the purpose of the main table ...
    
    Table: PeopleSongs  
        peopleID int foreign key references People(id)
        songID int foreign key references Songs(id)
        songPlayed binary(1)
    And that's it. NEVER duplicate the information in the SONGS table!

    And now each "People" record can have as many songs associated with it as you want. None, one, five...or more.

    And you only construct that result you called "intermediate table" WHEN YOU NEED it and using a JOIN query.

    Example:
    Code:
    SELECT P.id, PS.songPlayed, S.songName, S.songSinger, S.songMovie
    FROM People AS P, PeopleSongs AS PS, Songs AS S
    WHERE P.id = PS.peopleID AND PS.songid = S.songid

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by Old Pedant View Post
    Oesxyl, you are an optimist.

    I don't think he will read and understand that article.
    is op choice to read or not and I can't make assumption about what he/she understand or not but I make the assumption that he/she want to learn, .
    anyway this is more simple and with same subject, design and normal form:

    http://www.sitepoint.com/article/dat...gn-management/

    btw I was and I still am optimist,

    best regards

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,436
    Thanks
    75
    Thanked 4,370 Times in 4,335 Posts
    That's a nicely written and concise article, but I wish he had gone to the trouble to show the *corrected* forms of his tables (as well as the wrong forms), for clarity.

    I also wish he had come up with a single unified example, rather than having to change to a different example at each stage.

    So...good but not perfect. But still worth keeping in favorites for future posts.


  •  

    Posting Permissions

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