-1

I have a "Groups" table, a "GroupMembers" table, and a "MemberType" table. In the "GroupMembers" table I have a foreign key for "Group", a foreign key for "MemberType", and a lookup field called "Member", which is a lookup to one of one of 3 tables: "JobTitle", "LaborDepartments", of "LaborSubDepartments".

Basically, members of the groups can come from one of 3 tables, so I added the "MemberType" column so I know which table the link comes from.

So I guess my question is: is this normal? Is there a better way to do this?

Just looking for other people experience. I hope the situation makes sense.

I am using SQL Server 2008.

Thanks, Tim

Timothy Carter
  • 43
  • 3
  • 1
    I would say that anytime you want to use data in one lookup table against any of three differnt table you are in trouble. NOw you han have an FK relationship but it is almost useless becasue there are groups that are not appropriate to job title for instance. Without more information onthe actual use of this data and waht is actually stored ina l the tables is hard to recommend a better design. – HLGEM Aug 08 '14 at 20:25
  • Thanks for the comment HLGEM. I was expecting this comment. I know I didn't give a ton of info as to how this is used, and I figured this idea was flawed. You are correct, the (I'll call it) faux FK relationship I have to the 3 tables that hold possible members is useless. And as I started to play with this I quickly saw the issues. – Timothy Carter Aug 08 '14 at 22:46
  • I am going to use a view to pull all 3 "child" tables into one and give them a unique field (ID + TableId) and use that in my GroupMembers table. I know I'm writing gibberish at this point, as I am not giving any examples or details, but the bottom line is; I think I can figure something out. Thanks for your help. – Timothy Carter Aug 08 '14 at 22:55

1 Answers1

0

in case anyone stumbles upon this, here is a good discussion of what I'm up against Foreign key referring to primary keys across multiple tables?

I should have searched better first.

Community
  • 1
  • 1
Timothy Carter
  • 43
  • 3