DB design question

[NuJoizey]

Hello SQL Pros. I'm not sure this is the right forum to post this in, but I'll do so, and if I am mistaken, I apologize in advance...

I'm 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!

 

[RiverGuy]

I don't know your specifics, but based on what you've described, I would do something as follows:

Company
---------
CompanyID PK
OriginalName 
...other stuff


CompanyDefinition
---------
CompanyDefinitionID PK
CompanyID FK (Unique Constraint1)
StartDate (Unique Constraint1)
EndDate 
CompanyDefinitionName 
ParentCompany FK (recursive to CompanyDefinitionID)

If it's possible for more than one company to have ownership in a subsidiary, then you can always remove the ParentCompany foreign key and create a CompanyOwnership table with a one-to-many relationship.

 

[mrdenny]

Why not put a unique constraint on the ComputerId, NameChangeEventId, and RoleId columns?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog