Jeff Sabarese wrote:
> did you look at <a href=http://www.crowbar.psulionsden.com/crowbarRDB_sketch.htm>my
> table</a>? if you follow that link, you'll see that i do in fact have an "artist_info" table, the very
> table which originally creates those Artist_Id's.
Based on the URL, I expected a diagram but I got a table of tables, so I didn't look in detail at it - my mistake ;-)
> i formatted the Concerts table in its present manner for a reason-- because most concerts have
> three bands per night,
You say most - so not all. What will happen when there are four?
> truly, to better Normalize my Concert info table, i would need to have at least one currently
> nonexistent field (in place of the 3 artist_id fields)-- now trust me, i don't speak from
> experience, but maybe it would be a TEXT field named "artists" where i could list 1, 2, 3, or
> more artists for any given concert (as some concerts have only one featured Artist, some three
> Artists, etc), that TEXT field could (if possible) be populated by INSERT using the Artist names from
> the Artist table
But that would be worse! In your current model you have the artist_id's at least in their own column, where they are individually accessible. Putting them all in one column would greatly complicate individual access to them.
> the best option would be to use in that field only an INT to reference a new table designed to hold
> only multiple artist info in its primary key,
Another table is indeed the solution, but not exactly as you propose.
You could e.g. create a table as follows:
CREATE TABLE performances (
concert_id INT NOT NULL,
artist_id INT NOT NULL,
play_time TIME
PRIMARY KEY (concert_id, artist_id)
);
To add a concert where artists with id's (1, 10, 20) are playing, you could do (I simplify the insert in the concerts table):
INSERT INTO concerts (show_date) VALUES ('2005-10-10');
SELECT @lid := LAST_INSERT_ID();
INSERT INTO performances (concert_id, artist_id, play_time) VALUES
(@lid, 1, '20:00'), (@lid, 10, '21:00'), (@lid, 20, '22:00');
Now to find out when a certain artist is playing, you can do the following query:
SELECT
concerts.show_date,
performances.play_time
FROM concerts
JOIN performances ON performances.concert_id = concerts.concert_id
WHERE
performances.artist_id = $artid;
> (by the way, i know those HTML tags i added don't work-- but this forum is SO annoying in that
> functional aspect,
I know, it's a pain.
--
felix
Please use
BBCode to format your messages in this forum.