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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)