Mysql table architecture suggestion needed

Question

Existing system - I have existing Users and Tutor_Details tables in my system. - There are two types of users - tutors and students. Users and Tutor_Details tables are linked by id_user foreign key.

New requirement - Every tutor can have some of the following credentials:-

Certified
Experienced
Other

A tutor can have a maximum of 3 and minimum 1 credential for now. For every credential specified, the tutor can add some description too.

Right now there are 3 credentials but later there may be more.

What would be the best way to store the credential info. Tutors may be searched by credentials. While viewing a tutor details, all his credentials should be displayed.

I was thinking about the following structure:-

A new Credentials table like-

CREATE TABLE IF NOT EXISTS `Credentials` (
  `id_credential` int(10) unsigned NOT NULL auto_increment,
  `credential` varchar(255) default NULL,
  PRIMARY KEY  (`id_credential`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

INSERT INTO `Credentials` (`id_credential`, `credential`) VALUES
(1, 'Certified'),
(2, 'Experienced'),
(3, 'Recent Student'),
(4, 'Other');

If new credentials are added later, they are defined here.

And one new Tutor_credential_map table which will contain one record for every credential of a tutor

CREATE TABLE IF NOT EXISTS `Tutor_Credential_map` (
  `id` int(10) NOT NULL auto_increment,
  `id_tutor` int(10) NOT NULL,
  `id_credential` int(10) NOT NULL,
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

That makes things easy for maintenance point of view, but if I want to fetch all the Tutor info as stored in Tutor_Details table along with all his credentials in a single query I get as many result sets for a tutor as there are credentials. This query:-

select td.id_tutor, tcrm.* from Tutor_Details as td inner join Users as u on td.id_user = u.id_user join Tutor_Credential_map as tcrm on td.id_tutor = tcrm.id_tutor join Credentials as cr on tcrm.id_credential = cr.id_credential where td.id_tutor = 23

Any idea to keep the separate tables as well as fetch a single tutor details record for each tutor with all the credentials info? Or is there a better way?

Thanks

Answer 1

You said "There are two types of users - tutors and students. Users and Tutor_Details tables are linked by id_user foreign key".
Can there be multiple tutors per user, i believe not. Also can a user be a Tutor and a student.
If in both the above cases the answer is "no" i suggest "User_Credential_map" instead of "Tutor_Credential_map". id_tutor can be id_user.

    CREATE TABLE IF NOT EXISTS `User_Credential_map` ( 
      `id` int(10) NOT NULL auto_increment, 
      `id_user` int(10) NOT NULL, 
      `id_credential` int(10) NOT NULL, 
      `description` varchar(255) NOT NULL default '', 
      PRIMARY KEY  (`id`) 
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 

Now to answer your question based on the above obesrvations.

    SELECT 
        Users.*, 
        Tutor_Details.* , 
        User_Credential_map.* , 
        Credentials.* 
    FROM 
        Users, 
        Tutor_Details, 
        User_Credential_map, 
        Credentials 
    WHERE 
        Tutor_Details.id_tutor = 23 
        AND Tutor_Details.id_user = Users.id_user 
        AND Tutor_Details.id_credential = Credentials.id_credential 

Answer 2

It's the nature of the SQL JOIN that multiple rows will be returned for each credential matches.

The easiest way to handle this would be by processing outside of MySQL using whatever lanuguage/system you are using to run the query in the first place.

As far as I can tell, your structure is just fine!