| Desing issue - Please tell me if i am right [message #205853] |
Mon, 27 November 2006 23:09  |
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 #205879 is a reply to message #205853] |
Tue, 28 November 2006 00:30  |
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 |
Frank
Messages: 7901
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 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)