From Newsgroup: comp.databases.mysql

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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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 companyrCOs name, a unique brandname, a unique website a unique vat
but not a unique address or a unique place/building for this reason IrCOll create another table named companyplaces (linked by a fk to companies)
where, for every place/building, IrCOll 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 companyrCOs 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
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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"

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql

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/
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.databases.mysql


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
--- Synchronet 3.21d-Linux NewsLink 1.2