Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GoDaddy
Yak Posting Veteran
64 Posts |
 Posted - 2008-11-21 : 12:21:14
|
| Hi I am kinda new in this db design. I have tables : Crew, Employee, EmployeeCrew(crewId, employeeId)Now I have a new table called Plant. So a team can only be in one Plant but the Plant can have many crew in it. An employee can also be in only one plant but the plant can have many employee. So in the Crew table, I added a plantId. In Employee, also added plantId.But there is this EmployeeCrew table in the middle. Employee can be associated to many crew. A crew can have many employee. My question is, is there a good way to kinda keep the integrity between crew/employee/plant. Why we have crew/plant association and employee/plant association is because we don't have to make the creation of crew mandatory. When the employee changes plant for example, is the employee is associated to the a crew. Then this change shouldn't be allowed because the plant of the crew is not the same as the employee. Kinda need to keep the integrity. So, the only want to change the plant on the employee is remove the association with the crew, then the change to another plant will be possible. Same issue with the crew. Thanks for the help |
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 2008-11-21 : 12:22:53
|
| I guess one of the solution would be with triggers on update |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2008-11-21 : 15:40:53
|
| That's probably the best way, although it's not great. The real question is whether it's important to enforce the Employee-Plant relationship. I can see a scenario, for instance, where an inspector could legitmately belong to crews in different plants. Same thing with senior managers, anyone who might have overall management or visitation responsibilities.If the reality of what you're trying to model allows for that, then it's not worth making the data try to fit the constraint, even if the exception is rare.Another thing you can do is have a default "Unassigned" crew for each plant, and insert that ID for individual employees. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-21 : 21:06:40
|
| Include plant ID in your employeeCrew FK? |
|
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 2008-11-24 : 09:54:01
|
| yeah I thought of including the plantId in employee crew, but its just hard to explain to someone else the reason why. So I decided to go with triggers, no need to add a plantId in the middle table and its easier to explain the reason behind those triggers.thanks for all the suggestions |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-25 : 07:46:57
|
| Hard to explain, but is it right? If you know and can prove it's right (requirements etc) then you have already thought through and should have enough of a case to explain it. If it's not then, quite rightly, you should change it.Personally I think a proper data model explains itself and works quite without triggers. There are always implementation exceptions (e.g. audit tables) but I think this falls into the data model category. It's up to you in the end of course. |
|
|
|
|
|
|
|
|
Design question