[njasm]

Hello.

I'm a proficient mysql user, and i believe that the problem i'm facing could be easily resolved with a more deeper knowledge of mysql.

any help is welcome!

Problem:

I have 2 columns named 'artist' and 'release' (schema below)
Release table have a field artist_id that references artist table, id field.
When admin try to add a new Release (musical release) created lets say by a single artist everything works fine.

Problem arised when the admin try to add a musical release created by two different artist, lets say
U2 and Marilyn Manson - Bloddy Love Album

at first glance i added

multiple => true

to the dropdown and i can recieve the data as array and thought. Ok need to convert this array into a single integer and set it into artist_id ...

Release Controller

	public function actionCreate()
	{
		$model=new Release;

		// Uncomment the following line if AJAX validation is needed
		// $this->performAjaxValidation($model);

		if(isset($_POST['Release']))
		{
            // since mysql field integer PRIMARY KEY, AUTO_INCREMENT
            // first record starts at 1, is safe to use 0 to implode
            if (count($_POST['Release']['artist_id']) > 1) {
                $_POST['Release']['artist_id'] = (int) implode(0, $_POST['Release']['artist_id']);
            } elseif (count($_POST['Release']['artist_id']) === 1) {
                $_POST['Release']['artist_id'] = (int) $_POST['Release']['artist_id'];
            }

			$model->attributes=$_POST['Release'];
            // code goes one..

then i'll get artist_id = 1023 ;
and get a DATABASE CONSTRAIN VIOLATION because i don't have any artist in the artisttable with id = 1023;

so i'm stuck.. i know i could remove the relation, create a pivot table between release and artist.
but that would make me do alot of requests to mysql just to server a single release/view/$id

isn't there a more elegant way to accomplish this need with minimum mysql requests possible?

below are Artist model and Release model code.

Artist.php

	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'releasesRelation' => array(self::HAS_MANY, 'Release', 'release_id'),
		);
	}

Release.php

	public function relations()
	{
		// NOTE: you may need to adjust the relation name and the related
		// class name for the relations automatically generated below.
		return array(
			'formatRelation' => array(self::BELONGS_TO, 'ReleaseFormat', 'format_id'),
			'typeRelation' => array(self::BELONGS_TO, 'ReleaseType', 'type_id'),
            'artistRelation' => array(self::BELONGS_TO, 'Artist', 'artist_id'),
		);
	}

schema

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Table structure for table `artist`
--

CREATE TABLE IF NOT EXISTS `artist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL COMMENT 'project name.',
  `description` text COMMENT 'artist description WYSIWYG editor.',
  `image` varchar(128) DEFAULT NULL COMMENT 'image link.',
  `site` varchar(128) DEFAULT NULL COMMENT 'official site link.',
  `facebook` varchar(128) DEFAULT NULL COMMENT 'facebook page',
  `twitter` varchar(128) DEFAULT NULL COMMENT 'twitter page.',
  `soundcloud` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='artists table' AUTO_INCREMENT=4 ;

--
-- Table structure for table `release`
--

CREATE TABLE IF NOT EXISTS `release` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `region` varchar(128) DEFAULT NULL COMMENT 'worldwide release?',
  `format_id` int(11) DEFAULT NULL COMMENT 'release format, digital, cd, vinyl etc.',
  `type_id` int(11) DEFAULT NULL COMMENT 'Type of release, singlel, ep, album or VA.',
  `date` date DEFAULT NULL COMMENT 'Release Date field',
  `label` varchar(128) DEFAULT 'Cut Loose Music',
  `artist_id` int(11) DEFAULT NULL COMMENT 'Artist id',
  `name` varchar(128) DEFAULT NULL COMMENT 'Release Name.',
  `cover` varchar(128) DEFAULT NULL COMMENT 'cover link, bigger size',
  `code` varchar(32) DEFAULT NULL COMMENT 'release code ex. CLM0001',
  `description` text COMMENT 'release description. to be used with a WYSIWYG editor.',
  `tracklist` text COMMENT 'tracklist, to be used with WYSIWYG editor.',
  `sample` varchar(128) DEFAULT NULL COMMENT 'link to sample mp3 file.',
  PRIMARY KEY (`id`),
  KEY `fk_release_release_format` (`format_id`),
  KEY `fk_release_release_type` (`type_id`),
  KEY `FK_release_artist` (`artist_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='releases table.' AUTO_INCREMENT=7 ;

--
-- Table structure for table `release_format`
--

CREATE TABLE IF NOT EXISTS `release_format` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `format` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Table structure for table `release_type`
--

CREATE TABLE IF NOT EXISTS `release_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `release`
--
ALTER TABLE `release`
  ADD CONSTRAINT `FK_release_artist` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_release_release_format` FOREIGN KEY (`format_id`) REFERENCES `release_format` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_release_release_type` FOREIGN KEY (`type_id`) REFERENCES `release_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

thanks for your help...

[U4EA]

Sounds like U2 are scraping the bottom of the barrel these days

Can I ask where it is getting an id of 1023 from?

I think the only answer to this one is a junction table like release_artist. I would imagine it's very easy to start without one and be a bit peeved if you suddenly realise you need one and have to remodel the DB as a result.

[njasm]

yeah you never know the future in the music industry

regarding 1023, it was just a concatenated method i found to put in the same integer two or more artist

1 is Artist.id field for U2, 23 is Artist.id field for Marylin Manson

and i was imploding(0, $_POST['Release']['artist_id']) the array received from the form post, and convert it to integer to add it to database (release model, field artist_id) but since i've a belongs_to relation, mysql doesn't let me add that int to that field coes doesn't found any id = 1023 under Artist.id field

code is at Release controller actionCreate() see above, it's the first lines.

[U4EA]

I think your only answer to this one mate is a junction table release-release_artist-artist.

If you add in a concatenation of the two artist as a single artist, it's bad DB design (as as I am aware) and will cause you SELECT problem etc when looking up the DB unless you use LIKE %U2% etc. I think that is causing you more problems than it is worth, especially as the DB starts to grow. I have been involved in large backfill data cleansing operations and, believe me, it's no fun

[CodeButterfly]

I agree with U4EA. You need to create a many to many relationship in the database using a junction table. Your reason for not doing so is not valid. Databases were made to handle this type of stuff extremely efficiently. Remember:

Quote

premature optimization is the root of all evil

and

Quote

The First Rule of Program Optimization: Don't do it.
The Second Rule of Program Optimization (for experts only): Don't do it yet.

If you truly have a speed problem you can fix a lot of it with caching (which you can worry about later).

[njasm]

hey thank you both for the help and quotes.

I have implemented this junction table approach, and its seems everything is working as expected.

Right now, after validating all form values, and confirming that the artist or artists exist in DB, I am using Yii DAO, I didn't created a model for the 'assign_artist_release' table.

this is the code I have

    static public function addArtistToRelease($release_id, $artistArray = array()) {
        $sql = "INSERT INTO assign_artist_release (artist_id, release_id) VALUES (:artist_id, :release_id)";
        $command = Yii::app()->db->createCommand($sql);
        
        foreach ($artistArray as $artist) {
            $command->bindParam(':release_id', $release_id, PDO::PARAM_INT);
            $command->bindParam(':artist_id', $artist, PDO::PARAM_INT);
            
            $command->execute();
        }
        
        return;
    }

And from a MVC Architecture view and best practices, where would you put this function? would you put it inside the Release Model (the model responsible for the CRUD operations regarding Music release)? or the Release Controller? or even create the assign_artist_release model, and add that function into it, even knowing that you will use only this piece of code, and why?