Hi James K. Lowden, Sorry. My backup application pulls tracks from albums and upload to server. Here, I need to store these Albums and Tracks information into database. From my application point of view, Every Track is a file.File may be track. When same track avail with different albums, then database can have single file reference. For ex: Track 'abc' avail in two albums. I can avoid duplicate row for the same file in AllFiles (Table). Any suggestions?
I considered 'employee' table reference. Thats why, I made singular. It makes sense for me to remove 'Tbl' and add plural for track/album. Thanks for suggestions. dd On Fri, Nov 8, 2013 at 7:47 AM, James K. Lowden <jklow...@schemamania.org> wrote: > 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
