Dan Sully wrote:

> * vidur (AT) lists (DOT) slimdevices.com <vidur (AT) lists (DOT) slimdevices.com> shaped the
> electrons to say...
>> CREATE TABLE songs (
>> TITLE varchar, -- title
>> TITLESORT varchar, -- version of title used for sorting
>> GENRE varchar, -- genre
>> GENRE_ID integer, -- genre object
>> ALBUM varchar, -- album
>> ALBUM_ID varchar, -- album object
>> ALBUMSORT varchar, -- version of album used for sorting
>> ARTIST varchar, -- artist
>> ARTIST_ID integer, -- artist
>> ARTISTSORT varchar, -- version of artist used for sorting
>> COMPOSER varchar, -- composer

> Can I make a couple suggestions before this gets too deep?
> Ditch the _ID notation - just have a has_a() reference from artist,
> genre, etc. The code will flow more naturally.
> my $genre = $track->genre();
> And if you really wanted the ID, $genre->id();
> Otherwise you'll end up with duplicate information.
> Also - track instead of song? Not everything is music.


Hope you don't mind that I'm replying to the list. As you noted in a
subsequent message, this is defintely a transitional schema. There
should really be a many-to-many relationship betweeen tracks and genres,
artists, albums. Both the string and ID versions of the latter will need
to go. The transitional schema was to support the existing Info.pm
search-pattern based API.

I also agree that _ID suffixes in column names aren't a good idea and
that Track makes more sense than Song. I used the _ID suffix in the
transitional schema to differentiate between the object reference and
the redundant string that I used for pattern matching. Again, both will
probably be replaced by tables supporting many-to-many relationships.