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!
Link Multiple Users To Table
Moderator: General Moderators
- 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
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.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!
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.
Re: Link Multiple Users To Table
This could be done in one table but it would wreak. 
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.
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.
Re: Link Multiple Users To Table
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.s.dot wrote:This could be done in one table but it would wreak.
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.
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?