View Full Version : How to use lookup table

04-11-2009, 01:19 PM

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.


04-11-2009, 02:40 PM
this will probably help you to design your database:


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

1.2 - cross-posting:


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

Old Pedant
04-11-2009, 08:05 PM
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:

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.


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

04-11-2009, 09:04 PM
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:


btw I was and I still am optimist, :)

best regards

Old Pedant
04-11-2009, 09:32 PM
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.