Re: SQL Database design question

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:1106907752.312855.40980_at_c13g2000cwb.googlegroups.com...
> You prevent duplication in the EmployeeBranches table as in any other
> table: with a PK or UNIQUE constraint. However, your constraints have
> to allow an employee to be added without a corresponding entry in
> EmployeeBranches, otherwise you could never add new rows to the
> Employees table.
>

This is exactly my problem.
I need to ensure that every employee created has and maintains at least one entry in the EmployeeBranches table.
Otherwise I'm going to have rogue employees that won't be able to work anywhere!!

This was why I was thinking of adding an Ee_Branch column in my employee table that is not NULLable and have it correspond to an entry in the Branch table. This could be used as their primary branch location. I suppose I could then use my EmployeeBranches table to assign them to other branches, ensuring that it's different to it's primary branch. But then this isn't exactly efficient.

Alternatively, I could get my web front end to ensure that on creating a new employee, it automatically adds an entry to the EmployeeBranches table too. But how do I prevent deletion of this initially created record, ensuring that each employee has at least one branch assigned to it??

This is becoming very confusing!

Any other suggestions?

Dan Received on Fri Jan 28 2005 - 11:52:09 CET