Database architecture problem

[edek]

Hi,

I have two datatables: Projects and Employees. Projects :
ID
NAME
EMPLOYEES Employees :
ID
NAME

I need to connect one project with multiple employees. Do I need to dynamically create separate table ' RelatedEmployees_<unique number here> ' and store this table's name in field EMPLOYEES of table Projects to achieve this???

Please help...
Thanks in adv.

[Ancient Dragon]

Of course you don't. Don't you know SQL language? If not, then get a good book and learn it.

SELECT  Projects.*
from Projects, Employees
where Projects.id = Employees.id
and Projects.name = Employees.name

[xga]

Hi!
Sorry for my English =)
How tables are related?
If field EMPLOYEES in table PROJECTS related to ID in EMPLOYEES, you can write

select P.NAME PROJNAME, E.NAME EMPNAME
from PROJECTS P, EMPLOYEES E
where P.EMPLOYEES = E.ID

But you give small info about tables struct and relations...

[edek]

Hi!
Sorry for my English =)
How tables are related?
If field EMPLOYEES in table PROJECTS related to ID in EMPLOYEES, you can write

select P.NAME PROJNAME, E.NAME EMPNAME
from PROJECTS P, EMPLOYEES E
where P.EMPLOYEES = E.ID

But you give small info about tables struct and relations...

The problem is actually: How to connect filed EMPLOYEES in table PROJECTS with multiple IDs in EMPLOYEES - i cannot have multiple values in single field...

[JerryShaw]

I think you should re-write the tables as such:

CREATE TABLE [dbo].[Project](
	[Project_ID] [int] IDENTITY(1,1) NOT NULL,
	[Project_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
	[Project_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Employee](
	[Employee_ID] [int] IDENTITY(1,1) NOT NULL,
	[Employee_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[Employee_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Project_Employee](
	[Project_ID] [int] NOT NULL,
	[Employee_ID] [int] NOT NULL,
 CONSTRAINT [PK_Project_Employee] PRIMARY KEY CLUSTERED 
(
	[Project_ID] ASC,
	[Employee_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

You get Project, Employee, and Project_Employee

Projects and Employees are related through the Project-Employee table.
This is a standard Normal Form design.
Now you can have unlimited Projects with unlimited employess per project.

Join the tables and set your criteria as needed.

Select Project_Name,Employee_Name 
from Project p 
LEFT JOIN Project_Employee pe on p.Project_ID = pe.Project_ID
LEFT JOIN Employee e on pe.Employee_ID = e.Employe_ID
ORDER BY p.Project_Name

[JerryShaw]

Sorry, typo.... you should not have to use LEFT JOIN on the employee table. You should however add foreign key constraints to force data integrity between all three tables.

To add an employee to a project, just add the Project_ID and Employee_ID combination to the Project_Employee table.

In normal form, you realize that Project is singlular, and Employee is singular. To stick with the rules, and allow an employee to belong to more than one project, you need the intermediate table that provides the relationship.
IOW, if you added Employee_ID to the project table, then a project can only have one employee.
If you add Project_ID to the Employee table, then an employee can only belong to one project.
If you use a delimited list, or XML field for employees in your project table as in your original structure... you are breaking the rules of normal form, plus the users of your table must have specialized knowledge of how employees are stored in this field... a big no-no.

Use of intermediate tables is standard practice in database modeling. Now you can expand each of the tables without harm to the relationship. Example: the intermediate table can include additional fields such as DateEmployeeAddedToProject, AddedByLoginAccountName, etc. (Items specific to this relationship, IOW, don't add employee age to the intermediate table.. it belongs to the employee table. Don't duplicate any field from either singlular table IOW dont add project_name to the intermediate table because it is already in the Project table. Get the point ?)

Hope this helps,
Jerry