Should I split tables

[BrookieOU]

I have an "Employees" Table that list the basic employee information (name,
address, phone #), but it also lists things like client assigned to, group
assigned to, certifications. I have a table for clients (lists address and
such) and a table for groups (list manager). Should I not have fields for
client assigned to and group assigned to in the "Employees" table. I read
somewhere I should only leave basic information in the "Employees" table and
create a third table where I can list the employees and the clients they are
assigned to and then another table to list the employees and the groups they
are assigned to. This seems very redundant to me.

I currently have people enter the information on a form and they are able to
select from a drop-down list that pulls from either the client table or the
group table (depending on what they are entering) so that the information
stays correct.

Should I separate the tables?

 

[Beetle]

It depends on the nature of the relationships between those entities.
Take Employees / Clients for example. The relationship could be one
of the following.

1) One Employee can have many Clients but a Client can only be related
to one Employee. This is 1:m (Employee:Clients). In this scenario you
would need EmployeeID as a foreign key field in tblClients.

2) A Client can be related to many Employees, but an Employee can only
be related to one Client. This is 1:m (Client:Employees), so you would
need ClientID as a foreign key field in tblEmployees.
(this scenario would seem unlikely)

3) One Employee can have many Clients and a Client can be related to
many Employees. This would be m:m, in which case you would need
to create a third (junction) table to define the relationship.

1:m = One-to-Many
m:m = Many-to-Many

 

[BrookieOU]

Actually, it is the second. We have contract employees that only work for
one of our clients. So one client can have 20 employees, but the employee
can only work for one client. So, all I need to do is add the ClientID field
to the Employees Table as the foreign key?

Also, thanks for the explanation, it's never been broken down that
understandable for me before.

Brooke

 

[Beetle]

Perhaps your "client assigned to" field could just become the field that
stores the ClientID. You may need to modify the data type.

You will also need to determine the proper structure for the Employee/Group
relationship.