How to store a old company which become a new company or part of a group?

3 views
Skip to first unread message

^Bart

unread,
Aug 5, 2020, 10:58:26 AM8/5/20
to
Hi Guys!

I need to do a db where I could store company's details and company's
products but what happen when a company for example named "X" become a
company named "Y"?

I should see the story of the product for example the product named "A"
is repaired ten times when the owner was company "X" and now is repaired
one time with the company "Y".

Sometimes it happen also a group of company will become just one...

^Bart

^Bart

unread,
Aug 5, 2020, 11:06:50 AM8/5/20
to
> I should see the story of the product for example the product named "A"
> is repaired ten times when the owner was company "X" and now is repaired
> one time with the company "Y".

Sometimes could happen to have a group of companies and I need to have a
specific table where I can store the name and I should link every
companies to this name/group:

CREATE TABLE companygroups
(
companygroup_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
picture LONGBLOB,
website1 VARCHAR(200) DEFAULT NULL,
vat VARCHAR(20) DEFAULT NULL,
note VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (companygroup_id)
)
ENGINE=INNODB;

The next table will store just few fields because a company could have a
unique company’s name, a unique brandname, a unique website a unique vat
but not a unique address or a unique place/building for this reason I’ll
create another table named companyplaces (linked by a fk to companies)
where, for every place/building, I’ll add a specific name, address,
phone, etc.

A company could be (or not, this is the reason why I set the field with
NOT NULL!) a part of a group of more than one company; oldcompanyname
is, if available, the old company’s name filtered just from companies
which have opened set to 0.

CREATE TABLE companies
(
company_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
companyname VARCHAR(100) DEFAULT NULL,
opened BOOLEAN NOT NULL,
companygroup_id INT(10) UNSIGNED DEFAULT NULL,
knownas VARCHAR(100) DEFAULT NULL,
oldcompanyname VARCHAR(100) DEFAULT NULL,
picture LONGBLOB,
website1 VARCHAR(200) DEFAULT NULL,
vat VARCHAR(20) DEFAULT NULL,
type ENUM ("Customer","Provider","CustomerProvider") NOT NULL,
note VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (company_id),
INDEX (company_id),
FOREIGN KEY (companygroup_id) REFERENCES companygroups (companygroup_id)
)
ENGINE=INNODB;

^Bart

Lew Pitcher

unread,
Aug 7, 2020, 3:19:12 PM8/7/20
to
On August 5, 2020 10:58, ^Bart wrote:

> Hi Guys!
>
> I need to do a db where I could store company's details and company's
> products but what happen when a company for example named "X" become a
> company named "Y"?
[snip]

See my 2020-02-25 reply to you in the comp.databases.mysql thread titled
"Something like a FK but in the same table"
(https://groups.google.com/d/msg/comp.databases.mysql/3ra_9uarsHU/oOEWBtjpCQAJ)
where I outline how to use a many-to-many table to hold company history.

HTH
--
Lew Pitcher
"In Skills, We Trust"

^Bart

unread,
Aug 8, 2020, 6:22:28 AM8/8/20
to
> See my 2020-02-25 reply to you in the comp.databases.mysql thread titled
> "Something like a FK but in the same table"
> (https://groups.google.com/d/msg/comp.databases.mysql/3ra_9uarsHU/oOEWBtjpCQAJ)
> where I outline how to use a many-to-many table to hold company history.

Ok, thanks! :)

> HTH

^Bart

Axel Schwenke

unread,
Aug 10, 2020, 4:00:20 AM8/10/20
to
On 07.08.2020 21:19, Lew Pitcher wrote:
> On August 5, 2020 10:58, ^Bart wrote:
>
>> I need to do a db where I could store company's details and company's
>> products but what happen when a company for example named "X" become a
>> company named "Y"?
> [snip]

Funny. I don't see the original query here.

> See my 2020-02-25 reply to you in the comp.databases.mysql thread titled
> "Something like a FK but in the same table"
> where I outline how to use a many-to-many table to hold company history.

If the user is running MariaDB, there is now an even better way of storing
and handling multiple (historical) versions of a record:

https://mariadb.com/kb/en/temporal-data-tables/

^Bart

unread,
Aug 16, 2020, 3:28:11 AM8/16/20
to

Thanks for your reply! :)

> If the user is running MariaDB, there is now an even better way of storing
> and handling multiple (historical) versions of a record:
>
> https://mariadb.com/kb/en/temporal-data-table
I didn't know it, I'll check it! :)

Have a nice day!
^Bart
Reply all
Reply to author
Forward
0 new messages