2. Re: Databases and Keys
- Posted by irvm at ellijay.com
Dec 10, 2001
On Monday 10 December 2001 05:42 am, you wrote:
> Hi All,
> I'm currently writting my own Music DB and wondered how other go about
> generating a record key. I thought about using the song title but no good
> because mor than one song may have the same name, so I considered joining
> the song name and the artist but again no good as the same song may be on a
> couple od CD's. The only othe thing I can think of is using date() as the
> key but it seems like a real waste and searching the db for a song title
> etc. would become slow. Alternativly how can I build many indexes.
You could use the UPC, it would be a unique identifier. (see note below)
As for locating things quickly, it's pretty easy to create your own indexes;
perhaps Junko's hash routines will help. Since the indexes only have to be
updated when a new CD is added to the collection, speed is not an issue.
Indexing 5,000 records takes about 1 sec. on my pc.
Schema for main table;
KEY = UPC
DATA = {ALBUM TITLE, ARTIST, { TRACK1, TRACK2, TRACK3.....} }
Indexes are stored in tables:
ARTIST , key is artist name
Howlin' Wolf , { 76732590829 }
-- have 1 album by Howlin' Wolf
Stevie Ray Vaughn , {774646587027, 44744906938, ...}
-- have two by Stevie Ray
TITLE , key is album name
Texas Flood, { 74646587027, 453534555 }
-- 2 albums have that name
Pride & Joy, { 44744906938 }
TRACK , key is song title:
Tell Me, { 74646587027 , 76732590829 }
-- done by Howlin' Wolf and Stevie Ray
Testify, { 7464387342 }
-- only one cd has a track by that name
Note: I just noticed that albums purchased in Mexico have no UPC.... I guess
you could look them up on the web, or just assign a random number.
Regards,
Irv
3. Re: Databases and Keys
- Posted by irvm at ellijay.com
Dec 10, 2001
On Monday 10 December 2001 05:42 am, you wrote:
>
> Hi All,
> I'm currently writting my own Music DB ........Alternativly how can I build
many indexes.
Guess I left off the part about how to build the indexes:
Input the album data:
UPC, {Album name, Artist name, {tracks...}}
We'll call the main table CD:
Open CD table, try to insert the record using UPC as the key, and the rest
of the data as ..erm.. the data. If it fails, you already have that album in
the database. Cope.
Then, open the ARTIST table, and try to insert a record using the Artist name
as the key, and the UPC as the data. If it fails, you already have another
album by that artist, so you read that record (use db_find_key), append the
new UPC to that record's data, and write it back using db_replace_data.
Do the same with the ALBUM table, using the Album name as key, and UPC
as data.
Do the same with the TRACKS table, using the song name as the key, and
the UPC as the data.
Very, very quick., and doesn't take much space to store a LOT of UPC's.
To look up a song title, for example; open the TRACKS table, use db_find_key
with the song title as the key. If it returns a number > 0, then that song
title exists in the db. Read that record; the data will contain 1 or more
UPC's. each of which contains a track by that name.
You can then open the main CD table, read the data for each of those UPC's
and use it as you want. Same if you input an artist (you get a list of UPC's
which are all albums by that artist), or an album title.
No seeking, sorting or searching involved (except that which EDS does for you)
Regards,
Irv
