Re: MySQL Queries - Full Question This Time :)

> I used varchars because the info for that field goes something like
> "CLA520" or "FOL982" etc. I had wondered about trying to break
> that up further into two fields, one for the three letter category and
> then another for the actual number.... you think that would make a
> difference?

ok, i mistakenly assumed your IDs were random keys

sorry

now the varchar makes sense

if in fact you have a category (CLA=classics?) code embedded, you would
want to take that out into a separate column, and then assign unique
numbers as keys (use mysql's AUTO_INCREMENT for this)


> The three are linked by the ID. A matching ID is supposed to pull album
> specific info from the "master" table (album title, date entered, label,
> etc.) and match all the artists from the "artist" table that have that
> same ID, and then the same for "track".
>
> Is that what you mean?

yes, that was my assumption from the way your join was written, but i still
think something's wrong

if master.ID = artist.ID represents the entire join condition between those
two tables, then either each artist can appear only on one album (doesn't
make sense for a music collection), or each album is keyed to the artist's
ID (doesn't make sense either), or, as i must conclude, the ID is actually
the album ID, and therefore there must be artist redundancy, with the same
artist appearing in the database multiple times, each time with the master
album key

> I've had a few thoughts on what I'd like to make different as far as
> the basic structure, but I haven't found a way that I could do so and
> feasibly convert the old data.

ah, that's easy, once the data is in the database, you can clone new tables
from it

create table newmaster
   ( newmasterID int not null auto_increment
   ,     PRIMARY KEY (newmasterID), INDEX (newmasterID)
             , UNIQUE (newmasterID)
   , newmasterCat char(3) not null
   , newmasterOldID varchar(15) not null
     [etc.]
   );

insert into newmaster
   ( newmasterID, newmasterCat, newmasterOldID [etc.] )
select
     null,  substring(master.ID,1,3), master.ID  [etc.]  from master

create table newartist
   ( newartistID int not null auto_increment
   ,     PRIMARY KEY (newartistID), INDEX (newartistID)
           , UNIQUE (newartistID)
   , artistname varchar(50) not null
   , artisttype varchar(50) not null
   );

insert into newartist
   ( newartistID, artistname, artisttype )
select DISTINCT
       null,  artist.name, artist.type  from artist

note the use of DISTINCT to get unique artist/type combinations

you might then do further analysis to see if the same artist is in more
than one artist type

hooking up the albums to the artists is a bit trickier, but similar


rudy

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA