January 28th, 2003, 06:31 PM
I am looking for some help regarding my database structure for an internal time recording system I am creating...
I am in the process of converting my ER-Diagram to relations. One of my entities called 'Project' has a number of fields, three of which are:IT_PROJMGR, BUS_PM and BUS_OWNER. I have another entity called 'Employee' with its primary key called EMP_ID. The three fields in the project entity are all foreign keys referencing the EMP_ID field in the employee table. So essentially, there is a many to many relationship between the two, even though there is only one employee that can take on any of these roles.
How can I go about transforming this to a set of relations? One method I thought of was to create a third entity - 'Projemps' and have PROJ_ID and EMP_ID as a composite key, then have a third field defining their position.
But I am not happy with this structure as it doesn't easily convey meaning. Is it possible to have the three fields remain in my project entity (relation) and have three foreign keys referencing the same field from the employee table???
Any help or suggestions you could offer me would be much appreciated.
January 28th, 2003, 11:18 PM
Can you diagram what is in your head? I have found that a diagram or an hierarchy chart, in your case, helps to shed light on a solution. A diagram could even help us help you.
January 29th, 2003, 04:30 PM
I have attached an ER-diagram of the structure I have created to date.
I have left the cardinality off the relationship between project and employee because this is partly what I am unsure about. I am also unsure of whether or not I can only have one relationship here, when I think that perhaps there should be three, since IT MANAGER, BUSINESS PM, and BUS OWNER all reference emp_num in the employee table.
I have the same sort of problem between the user and employee entites. But here I have simply created 2 relationships to show that both supervisor and emp_id reference the emp_id attribute in the employee table.
I'm not sure which method is correct, but I think its the second (ie. the one used between employee and user). However, if it is this one, can these entities then be transformed directly to relations???? I'm not sure whether or not you can have multiple fields in one table as foreign keys referencing the same field in another table.