Page 1 of 1

Link Multiple Users To Table

Posted: Tue Feb 01, 2011 2:53 pm
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!

Re: Link Multiple Users To Table

Posted: Tue Feb 01, 2011 3:06 pm
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.

Re: Link Multiple Users To Table

Posted: Tue Feb 01, 2011 10:09 pm
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.

Re: Link Multiple Users To Table

Posted: Wed Feb 02, 2011 12:52 pm
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?