Sreedhar.a wrote:
Hi,
I have created database using the joins method.
My database caontains 4 tables with the Artist(1),Album(2),Genre(or)
BGM(3),Combination of all these and Track names in one table.
I am having 4 tables as follows.
*
"create table ALBUM(AlbumId integer primary key,Album text);"
21 Album1
22 Album2
23 Album3
* "create table ARTIST(ArtistId integer primary key,Artist text);"
10 Madonna
11 Artist1
*
"create table BGM(BgmId integer primary key,Bgm text);"
31 rock
32 pop
*
"create table MUSIC(Id integer primary key,AlbumName
text,Album_TypeId integer,ArtistName text,Artist_TypeId integer,BgmName
text,Bgm_TypeId integer);"
1 Album1 21 Madonna 10 rock 31
2 Album2 22 Madonna 10 pop 32
If we want to delete a particular Artist from ARTIST table.
I need to delete all the records corresponding with that artist name in
MUSIC table also and we need to check for the albums and Genre(BGM) in Music
are not present after deleting that particular Artist and delete the records
in ALBUM and BGM Table .
Consider I have an Artist Madonna in ARTIST table. the user wants to delete
Madonna artist.
Currently,
1.We are deleting Madonna in ARTIST table.
2.Weare first reading the album ids of Madonna and Bgm id's
of Madonna in one buffer and then we are deleting that Artist Madonna in the
MUSIC table.
3.Now we will check wheather that Album ids and BGM ids in
buffer is still present in MUSIC table.If it does not present we will delete
it in the ALBUM and BGM table.If it still exists we wont delete it in ALBUM
and BGM table.
But if we do like this we got the desired result but buffer size is
incresing if records are increasing.
Is there any other method to solve deletion in multiple table.
Can anyone of you suggest how i can do the deletion.
Best Regards,
A.Sreedhar.
Hi,
I would first suggest that you normalize your database. By that I mean,
remove the redundant copies of the artist name, album name, and BGM name
from the music table.
Given these tables:
create table ALBUM(AlbumId integer primary key, Album text);
create table ARTIST(ArtistId integer primary key, Artist text);
create table BGM(BgmId integer primary key, Bgm text);
Your music table should probably look something like this:
create table MUSIC(
Id integer primary key,
AlbumId integer references ALBUM,
ArtistId integer references ARTIST,
BgmId integer references BGM
);
Now you can generate a table of results much like your previous music
table by joining these tables like this:
select Album, Artist, Bgm
from MUSIC
join ALBUM using AlbumId
join ARTIST using ArtistId
join BGM using BgmId;
The one thing to note here is that even though I have indicated that the
*Id fields are foreign keys in the Music table by adding the references
clause, SQLite does not do anything with that information. It is
basically a comment for human readers.
You can add triggers to the database that will automatically ensure
referential integrity. With these triggers defined, SQLite will handle
the the cascaded deletes for you. This means that it will automatically
delete all music by an artist when that artist is deleted. You can get
more info on these triggers at
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
Your code won't need to do these deletes and it won't need buffer space
to store intermediate results you are using to do the deletes manualy.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
