Something like a FK but in the same table

[^Bart]

Hi guys,

I'd like to have a table like this:

companies
--------------------------
id_company
name
oldname_fk_id_company

Why I should do it? Because sometimes a company named "X" after years
could change the name in "Y" but people who work there (and the chief!)
could be the same!

I could add a new company named "Y" but I'd like to add a feature where
I could link it the old name!

For example you can sell a laptop with three years warranty to the
company "X" but this company after a year changes the name in "Y" and if
I can see the company "Y" was "X" I could search products with "X"
search key!

^Bart

[Lew Pitcher]

^Bart wrote:

> Hi guys,
>
> I'd like to have a table like this:
>
> companies
> --------------------------
> id_company
> name
> oldname_fk_id_company

Yes? So?

It's not unheard of. It implies that there is a one-to-one relationship
between two rows of the table; and it's reasonable to hold that relationship
in the table. You might find it advantageous to normalize the relationship
into it's own table, should your SQL get too complex.

> Why I should do it? Because sometimes a company named "X" after years
> could change the name in "Y" but people who work there (and the chief!)
> could be the same!
>
> I could add a new company named "Y" but I'd like to add a feature where
> I could link it the old name!
>
> For example you can sell a laptop with three years warranty to the
> company "X" but this company after a year changes the name in "Y" and if
> I can see the company "Y" was "X" I could search products with "X"
> search key!

Have you thought of the scenario where two companies merge? Your schema
won't adequately handle this case.

[J.O. Aho]

In most countries the company will still have the same company
number/tax number, so would more likely do two tables in that case

companies
company_id (primary key)
tax_number
name
used_since (date)

company_name_history
company_id
name
used_since (date)

Each time you update you update the name, you store the old name with
it's "used_since" date to a history table and then you update the name
and used_since date.

As it's the same company but with a new name, it shouldn't become a new
entity in your system, it should still be the same, just display a new
name. You will still be able to find old names and it's far faster than
join the companies table multiple times with itself for you to figure
out all the 10 names a company has had.

Having a history table has the advantage that you don't usually need the
data, so it don't matter if it would have a lot of rows and be a bit
slow to get all the data.

Say we had company A which has changed name 10 times, we can make an
simple query

SELECT * FROM companies c LEFT JOIN company_name_history h ON
c.company_id = h.company_id WHERE c.name = 'A'

compare that with

SELECT * FROM companies c1
LEFT JOIN companies c2 ON c1.oldname_fk_id_company = c2.id_company
LEFT JOIN companies c3 ON c2.oldname_fk_id_company = c3.id_company
LEFT JOIN companies c4 ON c3.oldname_fk_id_company = c4.id_company
LEFT JOIN companies c5 ON c4.oldname_fk_id_company = c5.id_company
LEFT JOIN companies c6 ON c5.oldname_fk_id_company = c6.id_company
LEFT JOIN companies c7 ON c6.oldname_fk_id_company = c7.id_company
LEFT JOIN companies c8 ON c7.oldname_fk_id_company = c8.id_company
LEFT JOIN companies c9 ON c8.oldname_fk_id_company = c9.id_company
LEFT JOIN companies c10 ON c9.oldname_fk_id_company = c10.id_company
WHERE c1.name = 'A'

But say the company changes name once more, in the first case we don't
have to modify the query, but on the second one you would need to add

LEFT JOIN companies c11 ON c10.oldname_fk_id_company = c11.id_company

and for each name change the select will become slower and slower and
suddenly you will kill your database...

--

//Aho

[^Bart]

> In most countries the company will still have the same company
> number/tax number, so would more likely do two tables in that case

[CUT]

> Each time you update you update the name, you store the old name with
> it's "used_since" date to a history table and then you update the name
> and used_since date.

Good idea to have two different tables!

> As it's the same company but with a new name, it shouldn't become a new
> entity in your system, it should still be the same, just display a new
> name. You will still be able to find old names and it's far faster than
> join the companies table multiple times with itself for you to figure
> out all the 10 names a company has had.

Yes, it's true!

> Having a history table has the advantage that you don't usually need the
> data, so it don't matter if it would have a lot of rows and be a bit
> slow to get all the data.
>
> Say we had company A which has changed name 10 times, we can make an
> simple query
>
> SELECT * FROM companies c LEFT JOIN company_name_history h ON
> c.company_id = h.company_id WHERE c.name = 'A'

Thanks for this idea! :)

> But say the company changes name once more, in the first case we don't
> have to modify the query, but on the second one you would need to add
>
> LEFT JOIN companies c11 ON c10.oldname_fk_id_company = c11.id_company
>
> and for each name change the select will become slower and slower and
> suddenly you will kill your database...

Thank you very much to explain this good idea! :)

^Bart

[^Bart]

> Have you thought of the scenario where two companies merge? Your schema
> won't adequately handle this case.

Yes, it could happen and I should try to think how to "solve it"...

Thanks! :)

^Bart

[Lew Pitcher]

^Bart wrote:

>> Have you thought of the scenario where two companies merge? Your schema
>> won't adequately handle this case.
>
> Yes, it could happen and I should try to think how to "solve it"...

One solution would be to normalize your table to create an additional "many-
to-many" relationship table, and use it to join company entries.

Your

> companies
> --------------------------
> id_company
> name
> oldname_fk_id_company

becomes
CREATE TABLE companies (
id_company INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
)

CREATE TABLE company2company (
id_predecessor INTEGER UNSIGNED NOT NULL,
id_successor INTEGER UNSIGNED NOT NULL,
FOREIGN KEY (id_predecessor) REFERENCES companies(id_company),
FOREIGN KEY (id_successor) REFERENCES companies(id_company),
PRIMARY KEY (id_predecessor, id_successor)
)

When Company "A" and Company "B" merge to give Company "C", the
company2company table would contain
id_predecessor id_successor
"A" id_company "C" id_company
"B" id_company "C" id_company

When Company "C" renames to Company "D", the company2company table would
contain
id_predecessor id_successor
"C" id_company "D" id_company

When Company "D" splits into Company "E" and Company "F", the
company2company table would contain
id_predecessor id_successor
"D" id_company "E" id_company
"D" id_company "F" id_company

You can follow the changes from company "F" to "D" to "C" to "A" and "B" by
following the chain of id_predecessor values for each company_id

And so forth.

For what it's worth, my 2 cents...

--
Lew Pitcher
"In Skills, We Trust"