Home » SQL & PL/SQL » SQL & PL/SQL » Desing issue - Please tell me if i am right
Desing issue - Please tell me if i am right [message #205853] Mon, 27 November 2006 23:09 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
I am new to this forum, and I am facing a doubt regd a
design issue..

I have a category table something like this:


create table category(cid number(10),cname varchar2(20));


cid has values like 101,102...

cname is song categories like pop,rock,classical etc..

Now I have the detail table tsongs

 create table songs(cid number(10),songname varchar2(10),path varchar2(10))



now category is the master table....songs is detail table

each songname can come into more than one category
for ex: songname "mysong" can fall into 2 categories, pop
and rock (101,102)....

then how to store the data in the detail table, considering
cid is the foreign key here? and its primary key in category table

i think, that there should be a seperate row for each possible
combination in the detail table...

meaning mysong,101.....will be one row
mysong,102.......will be a seperate row

like that how many categories a song comes into, those many
rows will be there in songs table...

is this the right way to store the data in this scenario?
is there a better way?

Re: Desing issue - Please tell me if i am right [message #205871 is a reply to message #205853] Tue, 28 November 2006 00:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is called a many-to-many relationship (at least that's what we call them Wink)
Remove the cid and foreign key from the songtable.
Add a primary key column (e.g. id) to the songtable.
Add a table song_categories containing two columns: cat_cid and sng_id with foreign keys to both tables.
Re: Desing issue - Please tell me if i am right [message #205879 is a reply to message #205853] Tue, 28 November 2006 00:30 Go to previous messageGo to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
so the table structures should be

create table category(cid number(10) primary key,cname varchar2(20));

create table song(sid number(5) primary key,songname varchar2(20));

create table cat_song(cid number(10),sid number(5),
 constraint cat_song_fk foreign key(cid) references
 category(cid),
 
 constraint cat_song_fk1 foreign key (sid) references
 song(sid));



i think this is one to many with respect to song and cat_song

so while retriving we have to join the tables?

now, this is good, but what is wrong in adding a new column
to song table say, cids(varchar2)..and if each songname has several
categories, we can store those as 101,102,103...

and we can use like operator in combination of sid to
retrive data..

retrieval wise which one is faster?


[Mod-Edit: changed the code-tag so all statements fall into it. Frank]

[Updated on: Tue, 28 November 2006 01:55] by Moderator

Report message to a moderator

Re: Desing issue - Please tell me if i am right [message #205903 is a reply to message #205879] Tue, 28 November 2006 01:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would use the cids column in the song-table to store an enumeration of categories, there is no (formal) relation anymore between the two tables.
You would not be able to create a foreign key anymore, you could not store cids as number anymore, you would be forced to split the cids each time you would want to join.

To join song and category you would use a query like this:
select sng.songname
,      cat.cname
from   songs    sng
,      category cat 
,      cat_song csg
where  csg.cid = cat.cid
and    csg.sid = sng.sid


As a sidenote: Try to use a consistent naming convention for your tables. Don't mix multiples/singles as in songS/category
Re: Desing issue - Please tell me if i am right [message #205904 is a reply to message #205903] Tue, 28 November 2006 02:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Also, if you store multiple values in a single column, then the query to find what songs are in a given category becomes a full table scan of Songs, plus quite a bit of Substr/Instr work, rather than a simple index range scan.
The Link table solution is far superior.
Previous Topic: Filter data
Next Topic: Trigger after Update?
Goto Forum:
  


Current Time: Tue Dec 06 10:21:44 CST 2016

Total time taken to generate the page: 0.13918 seconds