CODEWITHSUNDEEP
mysqldatabase-schema
jogesh_pi
jogesh_pi

Reputation: 9782

Best schema design to store and fetch the multiple ids in mysql

This is second time that i can face this kind of issue to retrieve the data. 

CREATE TABLE `pm_projects` (  
    `project_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
    `project_name` varchar(255) NOT NULL,  
    `assigned_client` varchar(100) NOT NULL,  
    `project_detail` longtext NOT NULL,  
    `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    `project_status` tinyint(1) NOT NULL,  
    PRIMARY KEY (`project_id`),  
    KEY `assigned_client` (`assigned_client`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

On the above table i have a field assigned_client that holds the multiple id's of the clients that are assigned to the project with comma separated(3,4,...).

And i am trying to fetch the result on this table with the Assigned Client's Name(that is on my pm_users table) with JOIN, I tried the following: 

SELECT 
    p.project_id, u.user_name, p.project_name, 
    p.creation_date, p.project_status
FROM pm_projects p
LEFT JOIN pm_users u ON u.user_id
IN (
    'p.assigned_clients'
)

that returns the NULL value of u.user_name field.

Can i have to change my schema, if yes then how?
OR i am trying with wrong Query?

Upvotes: 0

Views: 94

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166626

I would go with a many to many link approach.

Something like

CREATE TABLE pm_project_client_link(
    project_id INT,
    client_id INT
)

That would allow you to write the query something like

SELECT 
    p.project_id, 
    u.user_name, 
    p.project_name, 
    p.creation_date, 
    p.project_status
FROM    pm_projects p INNER JOIN
        pm_project_client_link pcl  ON  p.project_id = pcl.project_id INNER JOIN
        pm_users u  ON  pcl.client_id = user_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You can use find_in_set for this:

on find_in_set(u.user_id, p.assigned_clients) > 0;

Note that there are no single quotes around p.assigned_clients. This is another error in your query (but even if you replaced it with back quotes, the query still wouldn't work).

However, the problem is your table schema. You should have a separate association table, with one row per user and assigned client.

Trying to store this all in one field will only lead to problems, overly-complicated queries, and performance problems in the future.

Upvotes: 2

Related Questions