On Thu, 7 Nov 2013 14:50:44 +0400
dd <durga.d...@gmail.com> wrote:
> 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));
You might guess from my email domain name that I take an interest in
posts like yours. And it's pretty good first cut, no pun intended. ;-)
As I read the above, we have
Tracks <-N--1- Albums
Files <-N--M-> Tracks
That is,
An Album has many Tracks
A File may hold many Tracks, and
a Track may be part of many Files
The latter statement looks specious. I would expect the relationship
to be
Files -1--N-> Tracks <-M--1- Albums
meaning that every Track would have a foreign key to its File and Album.
I would also guess the Track needs some further information -- offset or
index number or something -- in order to locate the Track in the File.
Similarly you might want to capture the Track's ordinal position on the
Album. ISTM likely that at least sometimes the order of the music in
the file will differ from the "official" information you'd find on the
cover, back when albums had covers.
I recommend you use the natural keys unless they present a problem. I
might use an ID for a Files and Albums, because the names are long
and might not be definitive: you might want to change the filename
and/or the Album name, and there's no particular reason you'd want to
update Tracks to match. But the Tracks table doesn't need a surrogate
key: a Track's file and intrafile location specify it uniquely.
Not that you asked, but I also suggest you consider dropping the "Tbl"
from the table names. Noting that at table is a table in its name is
like calling every file "data". It makes it harder to read and conveys
no information. I myself prefer plurals for table names (files,
tracks, albums) because each *row* represents the singular -- a file, a
track, an album -- and tables as we know are made of rows. But some
people find that pedantic, and they're not wrong.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
