0

Consider the following scenario:

Tables:

  • Employee (EmpId(PK), Name)
  • TeamMembers(TeamId(PK), EmpId(PK))
  • Project(ProjId(PK), TeamId)

I really want to avoid using composite PK, but the only way I see out of the problem is creating a Team table with only 1 column TeamId(PK) (i do not want to store any info associated with the team other than its members) (EDIT: if I create a team table, i'll add TeamMeberId to TeamMembers table and make it a PK)

Another problem with current setup is that I can't set a relationship for TeamId between Project and TeamMebers tables

Should I just create a 1 column Team table? What's the best approach in this case?

EDIT

just to clear things up, the only thing I want to know about that team is its existance, no additional info of any kind

EDIT2

Tables New Design (anything wrong with it?):

  • Employee (EmpId(PK), Name)
  • Team(TeamId(PK))
  • TeamMembers(TeamMemberId(PK), TeamId(FK), EmpId(FK))
  • Project(ProjId(PK), TeamId(FK))
Community
  • 1
  • 1
roman m
  • 26,012
  • 31
  • 101
  • 133
  • I am still not clear why you need a Team table at all. Especially with the new construct. If a team only exists to be assigned to a project then there is no need to track it. – Jeremy Apr 21 '09 at 20:56
  • @Jeremy: how would you set up a FK relationship on TeamId in ProjectTable without Team table? – roman m Apr 25 '09 at 21:19

6 Answers6

5

If the only thing interesting about a team is the fact that it exists, then there is nothing wrong with a Team table with just one column: TeamId. It ensures referential integrity from the TeamMembers and Project tables.

But I do not understand your objection against a composite PK. The columns TeamId and EmpId in the TeamMembers table are alreay a composite primary key.

Ronald Wildenberg
  • 31,634
  • 14
  • 90
  • 133
  • with composite PK, i can't make TeamId a FK in Project table ... i might be doing something wrong tho – roman m Apr 21 '09 at 20:30
  • 2
    Create the Team table, use it to maintain referential integrity (FK) for both the Project and TeamMembers tables. – Chris Shaffer Apr 21 '09 at 20:32
  • Ah, I understand. I would create the Team table as suggested. Whether or not you then want to add a TeamMemberId to the TeamMembers table is up to you. I would choose not to because it adds extra data to your database that is unnecessary. – Ronald Wildenberg Apr 21 '09 at 20:51
2

There is nothing wrong with this scenario. I'd do it.

On the other hand, you could hold other information in your Team table like a team Name or something.

Eppz
  • 3,178
  • 2
  • 19
  • 26
1

There is nothing wrong with a 1 column table. However you might want to consider what other attributes your Team table could have. For instance, a team name?

For the relationship between project and employees, you merely have to join through the TeamMembers table.

dar7yl
  • 3,727
  • 25
  • 20
  • if i'd have additional info to store in the Team table, I wouldn't be asking the question here :) – roman m Apr 21 '09 at 20:37
0

does EmpId really need to be a primary key in your TeamMembers table? you could just say that each team has many employees, and the relationships work out.

Scott M.
  • 7,313
  • 30
  • 39
0

Since it looks like there is a 1-to-1 relationship (correct me if I'm wrong) between Project and TeamMembers and you don't want to store additional info about the team, wouldn't it be easier to get rid of the TeamMembers table and go with a many-to-many linking table between Project and Employee

Employee (EmpId(PK), Name)
EmployeeProjects(EmpId(PK), ProjId(PK))
Project(ProjId(PK), <other project info>)

but to answer your original question. There is nothing particularly wrong with having a single column table.

Jeremy
  • 6,580
  • 2
  • 25
  • 33
  • Seconded. If there's no information about a team, does it really exist? Sounds to me like you're creating an artificial artifact with no real value. With this scheme, you can easily create a list of all employees working on a project, and list all projects an employee is working on -- what's not to love? – TMN Apr 21 '09 at 20:47
0

This is how I would structure the tables

  1. Employee (EmployeeId(PK), Name)
  2. Team (TeamID(PK))
  3. TeamMembers(TeamMembersID(PK), TeamId, EmployeeId)
  4. Project(ProjectID(PK), TeamId)

I like to have a PK being the table name, suffixed with ID.
This convention does have the side affect of sometimes creating seemingly redundant primary keys (as TeamMembersID) but it solves your composite key problem.

Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • my question was "Is there anything wrong with Team table (mainly with it having only 1 column)? – roman m Apr 21 '09 at 20:42
  • 1
    @rm: I was aware of that. Although I didn't explicitly said it, I assumed you would know by looking at the way I'd structure the tables that my answer to your question was "No". – Lieven Keersmaekers Apr 22 '09 at 06:05