> I could store the department and business attributes with the > employee, but without proper constraints the referenced department > could conceivably not correspond to the referenced business. Or I > could ensure that all businesses have at least one department, > defaulting to the business when the business has no department, but > then I'd be storing duplicate data. The other alternative I've come up > with is an exclusive constraint where the employee instance can only > reference a department or a business, but not both. > > None of these solutions seems ideal, although the exclusivity solution > seems like it would work the best, and I have had to create > exclusivity constraints before. So, am I missing a more obvious > solution, or am I even on track here?
I’ve found this « Universal Person and Organization Data Model » very useful to understand complex questions like that: http://www.tdan.com/view-articles/5014
Hope this helps, François Beausoleil
Thanks François. This is pretty generic stuff, but my first reading has got me thinking that I should at least pick up some ideas from it. I will give it a go. And I've got a new web site that I've now known about before, so thanks for that as well.
Regards, Nelson
I strongly suggest you read the writings of Joe Celko, he has been addressing this kind of problem for decades. Ultimately, it is very important not to confuse behaviour with entities (yes employment is behavioural)
Cheers
Thanks Robin. Ironically enough, our little local library has three books by Joe Celko, so looks like I may have a weekend of reading ahead of me.
