| Data modelling involving many-to-many relationship and parent child hierarchy [message #616504] |
Tue, 17 June 2014 14:00  |
 |
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  |
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 #616511 is a reply to message #616508] |
Tue, 17 June 2014 14:33 |
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:51EMP_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 |
|
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 #616521 is a reply to message #616520] |
Tue, 17 June 2014 15:18 |
|
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 #616529 is a reply to message #616524] |
Tue, 17 June 2014 21:21 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
