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!
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!
In most countries the company will still have the same company
number/tax number, so would more likely do two tables in that case
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'
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...
Have you thought of the scenario where two companies merge? Your schema
won't adequately handle this case.
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"...
companiesbecomes
--------------------------
id_company
name
oldname_fk_id_company
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 91:17:48 |
Calls: | 6,658 |
Files: | 12,203 |
Messages: | 5,334,161 |