Tag Info

New answers tagged

4

Since both PersonID and EmployeeID are candidate keys (and presumably will be implemented with uniqueness constraints) your proposed table design satisfies Boyce Codd Normal Form and therefore 3rd Normal Form as well. That doesn't necessarily make it a good idea. In principle, it's no bad thing to provide alternative identifiers for things but in this case I ...


-1

This approach does break a normalization rule in that employeeid and customerid are functionally dependent on personid. A database such as you describe could not be said to be in the Third normal form for this reason. Further expansion for discussion based on answer feedback: Let's assume that the tables under discussion take the following form: Person ...


2

Swapping is a 3-step process (for this and many other situations). Go via a temp id. Something like: Update `box_types` `bt` SET `bt`.`box_number` = "-1" where `bt`.`box_type_id` = "3" ; Update `box_types` `bt` SET `bt`.`box_number` = "5" where `bt`.`box_type_id` = "1" ; Update `box_types` `bt` SET `bt`.`box_number` = "7" where `bt`.`box_number` = "-1" ; ...


1

There is no "constraint" in MySQL to achieve your complex constraint. However, you could probably write a TRIGGER(s) that would check each INSERT and UPDATE to see if your requirements are violated. The TRIGGER could act like a constraint -- barf on your INSERT/UPDATE. I suspect (but do not know for a fact) that other DBMS also have no such constraint, ...



Top 50 recent answers are included