one to many mapping

Question

I have a problem of mapping some scenario into data base.Problem is something like this.

There are two entities called Employee(empID,empName) and Task(taskID,taskName).

• A employee can have ZERO-OR-MORE tasks.
• A task can have ZERO-OR-ONE employee.

What will be the table structure that should I follow..

I'm going to implement this scenario using spring MVC and hibernate in java.How can I fixed this issue..

Any idea?

Thanks in advance!

Answer 1

Looks like you may need another table called EMPLOYEE_TASKS:

ID - sequence, PK.
EMPLOYEE_ID, numeric, not null, FK to ID in the EMPLOYEE table.
TASK_ID - numeric, unique key, not null, FK to ID in the TASKS table.

Your 0-or-1 employees/task requirement is handled by the UK on TASK.TASK_ID. Your 0-to-many tasks per employee is handled by the EMPLOYEE_ID+TASK_ID pair. Because TASK_ID is unique, this pair doesn't need to be. In the real world, I'd probably make this an index.

Now I'd use hibernate's reverse engineering tools to create annotated models. We put the primary key sequence generator specifications in the reveng.xml file.

---

If every task had exactly one employee, this table would not have been needed. We could add an EMPLOYEE_ID to the TASK table but two tasks would likely have no EMPLOYEE. This means we couldn't make that column a UK. So we'd have a hard time using the database to enforce the zero-or-one constraint.