Link Multiple Users To Table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Link Multiple Users To Table

Post by jamgood96 »

Creating a database with users (employees) and customers. I want to link customers to employees. The customer can be linked to multiple employees based on what projects are going on. So say employee X is working with customer Y on a certain project, and employee Z is working with that same customer. Normally I would create a 'user_id' column in the customer table, and then insert the employees 'user_id'. How do I go about doing this when a customer could have multiple 'user_id's associated with them?

Thanks and I hope that's clear enough!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Link Multiple Users To Table

Post by AbraCadaver »

jamgood96 wrote:Creating a database with users (employees) and customers. I want to link customers to employees. The customer can be linked to multiple employees based on what projects are going on. So say employee X is working with customer Y on a certain project, and employee Z is working with that same customer. Normally I would create a 'user_id' column in the customer table, and then insert the employees 'user_id'. How do I go about doing this when a customer could have multiple 'user_id's associated with them?

Thanks and I hope that's clear enough!
Normally you would have a join table. employees -> employee_customer <- customers. So the employee_customer table would have a row with an employee_id and customer_id, there can be multiples. However, given your requirements you need to give a little more thought to the other data. Will the employees and customers only be linked because of a project in common? If so, then maybe a join table for employees assigned to projects and a table of customers assigned to projects.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: Link Multiple Users To Table

Post by s.dot »

This could be done in one table but it would wreak. :P
A new table will be needed to store customer/employee (and as AbraCadaver stated, potentially other fields like project)

I would have my table setup as follows

[text]id | customer_id | employee_id[/text]

Each time a new customer arrives, or a customer works with a different employee, a new row would be added to this table. With this information, you can easily query which customers are with which employees, and which employees are with which customers.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
jamgood96
Forum Newbie
Posts: 12
Joined: Fri Jan 21, 2011 12:19 am

Re: Link Multiple Users To Table

Post by jamgood96 »

s.dot wrote:This could be done in one table but it would wreak. :P
A new table will be needed to store customer/employee (and as AbraCadaver stated, potentially other fields like project)

I would have my table setup as follows

[text]id | customer_id | employee_id[/text]

Each time a new customer arrives, or a customer works with a different employee, a new row would be added to this table. With this information, you can easily query which customers are with which employees, and which employees are with which customers.
The problem still arises as to what happens when a single customer id is associated with multiple employee id's. I followed what AbraCadaver mentioned and set up a table for customers, one for employees, and one for the projects. This seemed to work well for the most part.

My next issue is that each project could have sub projects. So say the main project would be House Projects, but under that could be a number of sub projects, each with its own data. Would it be best just to setup multiple tables for each particular project type?
Post Reply