Home » Other » General » Data modelling involving many-to-many relationship and parent child hierarchy
Data modelling involving many-to-many relationship and parent child hierarchy [message #616504] Tue, 17 June 2014 14:00 Go to next message
avirup.sen@gmail.com
Messages: 5
Registered: June 2014
Location: India
Junior Member
Hi All,
I have a scenario wherein there are two entities

1) EMPLOYEE (T_EMP)
2) DEPARTMENT (T_DEPT)

There is many-to-many relationship between EMPLOYEE and DEPARTMENT (i.e. to say that an EMPLOYEE can work in multiple departments)

An employee also has a manager and employee's manager will vary with the department.
So, if an employee is working in two departments, the employee will have two different managers (one for each department).

To begin with the modelling, since an employee can work in multiple departments, we will have a bridge table between T_EMP and T_DEPT

T_EMP
EMP_PK NUMBER(28), --> PRIMARY KEY
EMP_ID VARCHAR2(100) --> NATURAL KEY

T_DEPT
DEPT_PK NUMBER(28), --> PRIMARY KEY
DEPT_ID VARCHAR2(100) --> NATURAL KEY

T_EMP_DEPT_BRIDGE
BRIDGE_PK NUMBER(28) --> PRIMARY KEY
EMP_REF_PK NUMBER(28)
DEPT_REF_PK NUMBER(28)
NATURAL KEY = EMP_REF_PK + DEPT_REF_PK

Now, I also want to maintain hierarchy using employee to manager relationship

I also need to ensure that an employee should report to another employee as a manger and the manager should be in the same department where the employee is associated to.

How should I model this data so that the rule is enforced in the data model?
Any help would be appreciated.

Basically, I want to ensure that employee and manager work in the same department. How will I ensure this using Foreign keys?

Thanks
Avirup
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616505 is a reply to message #616504] Tue, 17 June 2014 14:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
avirup.sen@gmail.com wrote on Wed, 18 June 2014 00:30

EMP_ID VARCHAR2(100) --> NATURAL KEY

Why VARCHAR2 datatype for emp_id column? Keep it as NUMBER.

Quote:
DEPT_ID VARCHAR2(100) --> NATURAL KEY

Same here too, have it as a NUMBER data type.

Quote:
Now, I also want to maintain hierarchy using employee to manager relationship

Why? Do you need a lookup table to maintain the employee-manager relationship? However, you can fetch it anytime using hierarchical query. So why to have another table for it?

P.S. - Why did you post this topic in general forum?
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616508 is a reply to message #616505] Tue, 17 June 2014 14:21 Go to previous messageGo to next message
avirup.sen@gmail.com
Messages: 5
Registered: June 2014
Location: India
Junior Member
EMP_ID is a string and cannot be kept as number. (Ex: A-0116621)
DEPT_ID is something like 'IT', 'FIN', 'HR'; you may call it DEPT_NAME if you want to, but that's the natural key
I don't think the datatype is important in this question; I am looking forward to a normalised data model solution wherein I can maintain and enforce certain relationships/business rules.

Rule 1) An employee can belong to multiple departments
Rule 2) An employee can have multiple managers
Rule 3) An employee should be associated to a manger only within the same department

If I have a table like T_EMP_MGR_DEPT with following three columns, my information is stored
EMP_REF_PK
MGR_REF_PK
DEPT_REF_PK
All of the above three will give my unique key in this table.

However, how will I ensure that the MGR_REF_PK (which is also a reference to EMPLOYEE) belongs to the same department where the employee belongs?
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616511 is a reply to message #616508] Tue, 17 June 2014 14:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
avirup.sen@gmail.com wrote on Wed, 18 June 2014 00:51
EMP_ID is a string and cannot be kept as number. (Ex: A-0116621)

Ok.

Quote:
DEPT_ID is something like 'IT', 'FIN', 'HR'; you may call it DEPT_NAME if you want to, but that's the natural key

Then define it as DEPT_NAME or have it in your COMMENTS. DEPT_ID is generally considered to be the primary key of department table as a NUMBER data type.

Quote:
I don't think the datatype is important in this question; I am looking forward to a normalised data model solution wherein I can maintain and enforce certain relationships/business rules.

You think so that data type is not important. I think is it utmost important during the design phase itself.

Quote:
If I have a table like T_EMP_MGR_DEPT with following three columns, my information is stored

Once again, why to have this 4th table? You have EMP, DEPT, MGR tables already. Follow normalization and therefore you can query the relationship among the three tables anytime.
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616512 is a reply to message #616511] Tue, 17 June 2014 14:38 Go to previous messageGo to next message
avirup.sen@gmail.com
Messages: 5
Registered: June 2014
Location: India
Junior Member
In my conventions, *_ID field is the natural key coming from the source
_PK is the primary key or surrogate key that will be engineered in my database.
Let's figure out the logical data model first, while doing the physical model we can think about data types and names.

There is no separate table for MGR. MGR is also an employee
We have the following tables
1) T_EMP --> For storing EMPLOYEE
2) T_DEPT --> For storing DEPARTMENT
3) T_EMP_DEPT_BRIDGE --> For storing many-to-many relationships between T_EMP and T_DEPT

I need one more place to store EMP to MGR relationship and that should be associated to DEPT as well.
Hope, my question is clear.

[Updated on: Tue, 17 June 2014 14:42]

Report message to a moderator

Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616519 is a reply to message #616512] Tue, 17 June 2014 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is there a one to one relationship between DEPT & MGR?
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616520 is a reply to message #616519] Tue, 17 June 2014 15:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Since an EMP belongs to more than one DEPT, the MGR also implicitly belongs to multiple DEPT. Is my assumption correct logically?
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616521 is a reply to message #616520] Tue, 17 June 2014 15:18 Go to previous messageGo to next message
avirup.sen@gmail.com
Messages: 5
Registered: June 2014
Location: India
Junior Member
Yes, that point is correct. Since an employee belongs to more than one department, manager can also belong to multiple department, since manager is just another employee. It's just that employee and manager also share a relationship, which also happens to be many-to-many as well.
An employee will have two managers, if an employee is associated to two departments.
As an example, let me give the following
EMP1 works for DEPT1 and DEPT2
EMP2 works for DEPT1 and DEPT3
EMP3 works for DEPT2 and DEPT4
EMP4 works for DEPT5

EMP1 can have EMP2 as a manager for DEPT1
EMP1 can have EMP3 as a manager for DEPT2

However EMP1 cannot have EMP3 as a manger in DEPT1.

[Updated on: Tue, 17 June 2014 15:19]

Report message to a moderator

Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616524 is a reply to message #616521] Tue, 17 June 2014 18:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
can one DEPARTMENT have more than one MANAGER?
can one MANAGER manage more than one DEPARTMENT?
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616529 is a reply to message #616524] Tue, 17 June 2014 21:21 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you put the manager relationship in the Bridge, but use the NATURAL KEY as the parent relationship instead of the PRIMARY KEY and share the same column DEPT_REF_PK between the Natural key and the self-referencing Foreign key, then the manager must work for the same department.

create table T_EMP_DEPT_BRIDGE
(
 BRIDGE_PK NUMBER(28) PRIMARY KEY
,EMP_REF_PK NUMBER(28) 
,DEPT_REF_PK NUMBER(28)
,MGR_EMP_REF_PK NUMBER(28)
,CONSTRAINT EMPREF_DEPTREF_UK UNIQUE (EMP_REF_PK, DEPT_REF_PK)
,FOREIGN KEY MGREMPREF_DEPTREF_FK (MGR_EMP_REF_PK, DEPT_REF_PK) REFERENCES T_EMP_DEPT_BRIDGE (EMP_REF_PK, DEPT_REF_PK)


Note - untested syntax. But you get the idea.

Ross Leishman
Re: Data modelling involving many-to-many relationship and parent child hierarchy [message #616601 is a reply to message #616529] Wed, 18 June 2014 14:33 Go to previous message
avirup.sen@gmail.com
Messages: 5
Registered: June 2014
Location: India
Junior Member
Thanks Ross. This is helpful.
Previous Topic: Puzzle n°- Traverse through 100 doors, find which are open/closed **
Next Topic: Use of Bloom Filters in Oracle Database
Goto Forum:
  


Current Time: Thu Mar 28 10:43:29 CDT 2024