November 4, 2008 at 4:19 pm
Hi,
I am looking for suggestions on database structure for the situation below.
I have a list of employees in a master table. The employees in the table come from more than one company. I would like those employees to be part of groups that have their own separate tables (Information/Fields). An employee can be a member of more than one group.
I was going to only create a matching ID in the group tables where the employee was a member of that group. This would allow a one to one relationship. I was then also going to create an extra field in the master table with Company ID.
Are there any considerations I should make in this instance, and is there a better way to set up the structure.
Thanks
November 4, 2008 at 5:09 pm
The employee to Groups relationship that you are describing is many-to-many. Many-to-many relationiships must be implemented with a junction or "mapping" table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 7:11 pm
Hi,
The mapping table will work to link the employees to a table listing the groups, thanks for the suggestion.
But I also have different tables (with different structure/fields) for each group. How do I them link the employees to a record in each of the relevant group tables?
The only way I can think is to insert the Employee ID into each relevant Group table, and then link the relationship as a one to one between each group table and the master employee table. (But I am unsure about any possible referential integrity issues).
Thanks again.
November 4, 2008 at 7:46 pm
You are going to have to be more explicit about these relationships you are trying to describe, because they are not at all clear so far. Please supply some examples of what you are talking about.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 4, 2008 at 11:29 pm
Hi,
This would be a basic outline.
-----------
Master Table
EmployeeID, Employee Name, EmployeeNumber, CompanyID, (Other Employee fields)
------------
Company Table (Linked to Master table by CompanyID)
CompanyID, Company Name, (Other Company fields)
-----------
Group 1 Table (Linked to Master table by EmployeeID)
EmployeeID, ProjectName, ProjectPosition, StartDate, Finish Date
------------
Group 2 Table (Linked to Master table by EmployeeID)
EmployeeID, CourseName, CourseNumber, CourseScore, StartDate, FinishDate
-------------
An employee can be in both group tables, one table only, or none of the group tables. I have included only 2 group tables for simplicity but there will be more.
Is this the best structure, or should I link the Master table to the Group tables in a different way.
Hope this makes sense.
Thanks
November 5, 2008 at 6:13 am
These do not seem well-normalized to me. It seems like you should have at least 2 different tables for each "group". One to describe the elements of the group and one to list the group membership. For instance I would structure Group2 like this:
Create Table Courses(
CourseNumber int
, CourseName varchar(50)
, StartDate
, FinishDate)
GO
Create Table EmployeeCourses(
EmployeeID int
, CourseNumber int)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 6, 2008 at 9:54 am
I'm wondering if it's really necessary to have separate tables for each group, because as things change, will there be more groups? Adding a table each time could be problematic. Some of the groups you've shown have fields that are common in data type and in general purpose, so you may be able to use the same fields for more than one group, and just have a Group ID field, with the groups themselves in the Group table.
This might waste some database space, but it might also provide more flexibility in terms of changes down the road. How large the groups are should also factor into this. It would be difficult to offer much more without more detail, such as the nature of the groups and the nature of the reporting needs for this data.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 1:04 pm
The thing is, Steve, that if you look at the examples given, it becomes clear that the "Groups" are not really groups in the typical sense: there is no commonality between the different "Groups" other than that they all relate to the employees. As such, they are really just various employee attributes/properties/relations. I.e., Tables that need to be related to EmployeeTable.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 6, 2008 at 1:17 pm
Barry,
I wasn't convinced by the examples that your perception of it was necessarily so, which is why I asked, and also why I was looking for more information.
There's always a limited EAV scenario, if the data volume and size aren't prohibitive... In this case, the idea would be a Group table and an EAV style group membership table, with GroupID, EmployeeID, AttributeName, and AttributeValue as it's fields, and the PK is all 4. I'm not a fan of EAV in general, as one can easily take that concept entirely too far, but within limits, it's often useful as it occasionally better represents the real-world relationships than other models do. It's not common, but it's not entirely unusual either. Just my two cents on it, anyway...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 1:22 pm
Maybe, but what I have seen so far is just standard Relational Data issues that have not been fully normalized yet. I haven't seen anything that would lead me to believe that the OP should use something other than the Relational Model.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply