Hello - I'm just looking for some feedback and some discussion from other DB pros about the viability of a DB design I came up with. The goal is to create the DB back end to an application feature that keeps track of company name history in the event of name change, buy out or merger such that there is a traceable account history.
I've come up with with this ERD:
http://www.jerseytransit.org/Docs/AliasConsolidation.gif
What bothers me about my design: It relies on a trigger to keep data integrity. For example, looking at my ERD in the above link, let's say Company A buys out CompanyB and CompanyC. Company A is the "acquirer" role and both CompanyB and CompanyC are "acquiree" role. But the data model allows for possible entry of the "acquirer" to more than one company, if there is some error. I think I could write a trigger to check that there is not already an "acquirer" in this scenario and to not allow the update, but this is not using the natural referential integrity of an RDBMS.
So my questions to you pros are: 1. Does this matter? 2. Is this a viable way of handling this? 3. Is there another way you can see to do this without coding a trigger?
Any discussion is much appreciated - Thank you for reading this over!