How to keep track of Company renames and mergers

[dathan]

I have been tasked with creating a database that will generate
requests for documents owned by corporations such as banks, phone
companies, etc. This would not be a problem were it not for the fact
that these companies change names and merge with other companies more
often than Hollywood actors get sent to jail.

Each of the records in the tblRequests table will link via a foreign
key to a record in the tblEntities table. What I need to figure out
how to do is always maintain a link to the latest entity data while
retaining a history of a companies name changes and mergers. It will
likely be necessary for me to run reports against this data, such that
I will need to know how many requests were made of, for example, AT&T
Wireless. Of course, over the years, AT&T has acquired multiple
cellular providers, most recently Cingular.

How could I structure the tables in such a way that querying the AT&T
entity record will pull not only requests made of AT&T, but also of
Cingular and all companies swallowed up by AT&T?

Any help will be most appreciated.
Curis

 

[Jeanette Cunningham]

Hi dathan,

This is thinking out loud, it is not a complete solution to your problem,
just a step along the way.
If the following happened:
You request docs from Cingular and keep a record of them and then Cingular
is taken over by AT&T
The history report shows the document requests from Cingular while it was a
separate company.
Why would you want this history to suddenly change and show that requests
from Cingular were really from AT&T?
I think this is what you are saying in your post.

Jeanette Cunningham

 

[Jamie Collins]

What I need to figure out
how to do is always maintain a link...

What do you mean by 'link'? An identifier?

...to the latest entity data while
retaining a history of a companies name changes...

Then don't use name as an identifier. Use whatever the bureaucracy
uses in your land to track corporate bodies, as most (all?)
governments do e.g. here in the UK, Companies House issue every
company with a unique reference number (see
http://www.govtalk.gov.uk/gdsc/html/frames/CompaniesHouseReferenceNumber-2-1-Release.htm);
if you need something that transcends national jurisdictions (e.g. a
UK company number could duplicate one from another nation state) then
consider something like DUNS (see http://www.govtalk.gov.uk/gdsc/html/frames/DunsNumber-2-0-Release.htm).

...and mergers.

Ownership is a more complex issue e.g. consider a group of close
companies where the parent company is identified as being the majority
share holder. I suspect things will vary between jurisdictions. Do you
have a spec or at least some business rules for us to work with,
please?

Jamie.

--

 

[Curis]

Why would you want this history to suddenly change and show that requests
from Cingular were really from AT&T?
I think this is what you are saying in your post.

Jeanette Cunningham

I may not have explained myself fully. I would actually want those
old requests to still show that they were made of Cingular, but the
new contact information for subsequent requests will be sent to AT&T.
As I said previously, this would be easy were it not for the fact that
I would also like to maintain that history of company names, and
mergers.

And actually, the company names are not really much of a problem,
either, because I will be using a unique identifier for each company
and preserving each change to a Entity record in a history table.
Where my thoughts are breaking down is how to keep track of the
buyouts, mergers, etc. Maybe I am making it more difficult than it
needs to be?

 

[croy]

On Mon, 17 Dec 2007 17:35:20 -0800 (PST),

I have been tasked with creating a database that will generate
requests for documents owned by corporations such as banks, phone
companies, etc. This would not be a problem were it not for the fact
that these companies change names and merge with other companies more
often than Hollywood actors get sent to jail.

Each of the records in the tblRequests table will link via a foreign
key to a record in the tblEntities table. What I need to figure out
how to do is always maintain a link to the latest entity data while
retaining a history of a companies name changes and mergers. It will
likely be necessary for me to run reports against this data, such that
I will need to know how many requests were made of, for example, AT&T
Wireless. Of course, over the years, AT&T has acquired multiple
cellular providers, most recently Cingular.

How could I structure the tables in such a way that querying the AT&T
entity record will pull not only requests made of AT&T, but also of
Cingular and all companies swallowed up by AT&T?

Any help will be most appreciated.

Hi Curtis,

This may not be very helpful, but this is the sort of
situation where I think it helps to view the database as a
model of the real world (as it applies to the data you want
to manage).

The trick is to understand the "real world" as much as
possible. In this case, asking questions like, "Are the
swallowed companies now subsidiaries of another company?";
"Are the swallowed companies still companies?"; "Do I want
to keep track of what a company's name has been through the
ages?"; "What are all the possible situations involving a
company's existense?: ... and on and on. Only after that
sort of exploration will the best data design make itself
apparent to you.

Others have surely tread these waters before you, but I'm
not one of them (and certainly not anyone you'd want to
follow very far on any quest! I've never been satisfied
with any database work I've done.).

 

[Dale Fye]

Curis,

My thought is that you would want to have some sort of CompanyHistory table,
which might contain fields for CompID, CompName, ParentID, EffDate, and maybe
even a text Action field. For Cingular, this table might look like:

CompID CompName ParentID EffDate Action
93 Cingular NULL 1/1/1997 Company formed
93 Cingular 27 6/6/2007 Boughtout by AT&T

This table would then give you the ability to identify each entity you would
need to query during any given time period. It is not an easy nut to crack,
but I think this might be a first start. On further examination, I might
also even add a ThruDate to each record (although the database purists among
us would probably disagree with that recommendation).

HTH
Dale




--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jeanette Cunningham]

Curis,
Sounds as though you have 2 separate issues.
You can easily update the new contact information when companies merge etc.
and the doc requests will be sent to the correct address in the future - no
problem.
As I see it the business of keeping track of the mergers etc is a separate
issue that doesn't really have any impact on whether the doc requests will
go to the correct contact address in the future.

Jeanette Cunningham