[sqlite] Need suggestion for database scheama

[dd]

Hi All,

  I am working on sqlite database schema for Music/Track files. I am
posting few tables schema here.

CREATE TABLE if not exists AllFilesTbl (Id INTEGER PRIMARY KEY
AUTOINCREMENT, file_path TEXT NOT NULL, file_type INTEGER NOT NULL,
UNIQUE(file_path));

CREATE TABLE if not exists AlbumTbl (AId INTEGER PRIMARY KEY
AUTOINCREMENT, AlbumId TEXT NOT NULL, AlbumName TEXT NOT NULL,
UNIQUE(AlbumId));

CREATE TABLE if not exists TrackTbl (TId INTEGER PRIMARY KEY
AUTOINCREMENT, TrackId TEXT NOT NULL, AlbumId INTEGER NOT NULL, Title
TEXT NOT NULL, UNIQUE(TrackId, AlbumId), FOREIGN KEY(AlbumId)
REFERENCES Album(AId));

CREATE TABLE if not exists FileTrackTbl (FId INTEGER NOT NULL, TId
INTEGER NOT NULL, UNIQUE(FId, TId), FOREIGN KEY(FId) REFERENCES
AllFiles(Id), FOREIGN KEY(TId) REFERENCES Track(TId));

    Can I achieve normalization by using above 4 tables to store music
files and albums in database?

    Any suggestions/improvements are welcome.

Thanks in advance.
dd
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users