...

Article 1 Under Category One

Posted Dec 22, 2022
Category One
...

Customers and Suppliers

First issue to decide on is whether or not to put your list of customers and supplier in separate tables. It would most likely be easier to handle inside your application if you do so, however real world does not prevent a company from being a customer and a supplier at the same time to the same business. So as for me, I put both of them in the same table which I call company.

CREATE TABLE company(
	id char(36) NOT NULL,
	acct_no char(10) NOT NULL UNIQUE,
	company char(100) NOT NULL UNIQUE,
	PRIMARY KEY (id)
)ENGINE=InnoDb;

INSERT company SET id = UUID(), acct_no = '100',
	company = 'ABC Corporation';
INSERT company SET id = UUID(), acct_no = '101',
	company = 'XYZ Enterprises';

Contact Information and Addresses

Putting contact information in a separate table from the company file provides for a lot of flexibilities. To begin with, you will typically need to store more than one contact information for each company a business is dealing with. One contact person may be for purposes of selling or marketing to and another may be for sending billing statements. Note that the unique key id_no is not extraneous, as it is used to uniquely identify the person.

CREATE TABLE contact(
	id char(36) NOT NULL,
	id_no char(10) NOT NULL UNIQUE,
	lname char(30) NOT NULL,
	fname char(30) NOT NULL,
	contact_nos text NULL,
	dept char(30) DEFAULT '',
	company_id char(36) NOT NULL,    
	PRIMARY KEY (id)
)ENGINE=InnoDb;

INSERT INTO contact SET id = UUID(), id_no = '1000', lname = 'Smith', fname = 'John',
	contact_nos = '999 1111', dept = 'Purchasing',
	company_id = (SELECT id FROM company WHERE acct_no = '100');
INSERT INTO contact SET id = UUID(), id_no = '1001', lname = 'Brix', fname = 'Sally',
	contact_nos = '999 1122', dept = 'Accounting',
	company_id = (SELECT id FROM company WHERE acct_no = '100');
INSERT INTO contact SET id = UUID(), id_no = '1002', lname = 'Yang', fname = 'Benny',
	contact_nos = '888 2222', dept = 'Purchasing',
	company_id = (SELECT id FROM company WHERE acct_no = '101');

Getting a list of the contact persons per company is a basic INNER JOIN query.

SELECT company, lname, fname, contact_nos
	FROM contact INNER JOIN company 
	ON contact.company_id = company.id 
	ORDER BY company, lname, fname;

Which gives the following output: 

+-----------------+-------+-------+-------------+
| company         | lname | fname | contact_nos |
+-----------------+-------+-------+-------------+
| ABC Corporation | Brix  | Sally | 999 1122    |
| ABC Corporation | Smith | John  | 999 1111    |
| XYZ Enterprises | Yang  | Benny | 888 2222    |
+-----------------+-------+-------+-------------+

Should a contact person switch to a different company, all that needs to be done is to change the value of the foreign key company_id.

But what if you need to retain the history of the contact person's associations with possibly different companies? For such a requirement the contact and company tables need to be joined by another table and the foreign key company_id in the contact table needs to be removed.

CREATE TABLE company_contact(
	id char(36) NOT NULL,
	as_of date NOT NULL,
	company_id char(36) NOT NULL,
	contact_id char(36) NOT NULL,
	contact_nos text NULL,
	dept char(30) DEFAULT '',
	PRIMARY KEY (id)
)ENGINE=InnoDb;

/** Smith joins ABC **/
INSERT INTO company_contact SET id = UUID(), as_of = '2020-01-01', 
	company_id = (SELECT id FROM company WHERE acct_no = '100'),
	contact_id = (SELECT id FROM contact WHERE id_no = '1000'),
	contact_nos = '999 1111', dept = 'Purchasing';
INSERT INTO company_contact SET id = UUID(), as_of = '2023-02-01', 
	company_id = (SELECT id FROM company WHERE acct_no = '101'),
	contact_id = (SELECT id FROM contact WHERE id_no = '1000'),
	contact_nos = '222 3333', dept = 'Logistics';

/** Smith moves to XYZ **/
INSERT INTO company_contact SET id = UUID(), as_of = '2020-03-01', 
	company_id = (SELECT id FROM company WHERE acct_no = '100'),
	contact_id = (SELECT id FROM contact WHERE id_no = '1001'),
	contact_nos = '999 1122', dept = 'Accounting';

Now, if you try to get the contact persons using the a query that simply joins the three tables, you will get inaccurate results. Smith appears as contact person for two companies.

+-----------------+------------+-------+-------+
| company         | as_of      | lname | fname |
+-----------------+------------+-------+-------+
| ABC Corporation | 2020-01-01 | Smith | John  |
| XYZ Enterprises | 2023-02-01 | Smith | John  |
| ABC Corporation | 2020-03-01 | Brix  | Sally |
+-----------------+------------+-------+-------+

To retrieve the correct list would now require more than a single query. Here is a stored function script to do just that.

DELIMITER $$

DROP FUNCTION IF EXISTS contact_latest$$
CREATE FUNCTION contact_latest(x_contact_id char(36))
	RETURNS char(36)
BEGIN
	DROP TEMPORARY TABLE IF EXISTS contacts;
	CREATE TEMPORARY TABLE contacts AS
	SELECT company_id, as_of FROM company_contact 
		WHERE contact_id = x_contact_id ORDER BY as_of DESC LIMIT 1;
	SET @company_id = (SELECT company_id FROM contacts);
	RETURN @company_id;
END$$

DELIMITER ;

The function creates a temporary table where the latest company association of the contact person is obtained. To get the latest company for Smith for example:

SET @contact_id = (SELECT id FROM contact WHERE id_no = '1000');
SET @company_id = contact_latest(@contact_id);
SELECT company FROM company WHERE id = @company_id;

Which gives the correct company and only one company for Smith.

+-----------------+
| company         |
+-----------------+
| XYZ Enterprises |
+-----------------+

Going back to the task of obtaining the list per company then we can just make use of this stored function:

SELECT company, as_of, lname, fname FROM company_contact
	INNER JOIN company ON company_contact.company_id = company.id 
	INNER JOIN contact ON company_contact.contact_id = contact.id
	WHERE company.id = contact_latest(contact.id);

producing the accurate output below:

+-----------------+------------+-------+-------+
| company         | as_of      | lname | fname |
+-----------------+------------+-------+-------+
| XYZ Enterprises | 2023-02-01 | Smith | John  |
| ABC Corporation | 2020-03-01 | Brix  | Sally |
+-----------------+------------+-------+-------+

Of course, the query works even if each company has more than one contact person.

INSERT INTO company_contact SET id = UUID(), as_of = '2020-03-01', 
	company_id = (SELECT id FROM company WHERE acct_no = '101'),
	contact_id = (SELECT id FROM contact WHERE id_no = '1002'),
	contact_nos = '777 1111', dept = 'Purchasing';

SELECT company, as_of, lname, fname FROM company_contact
	INNER JOIN company ON company_contact.company_id = company.id 
	INNER JOIN contact ON company_contact.contact_id = contact.id
	WHERE company.id = contact_latest(contact.id);

The output of the last query being:

+-----------------+------------+-------+-------+
| company         | as_of      | lname | fname |
+-----------------+------------+-------+-------+
| XYZ Enterprises | 2023-02-01 | Smith | John  |
| ABC Corporation | 2020-03-01 | Brix  | Sally |
| XYZ Enterprises | 2020-03-01 | Yang  | Benny |
+-----------------+------------+-------+-------+

Company addresses can be treated similarly, but depending on the scenario may not need the creation of a stored function. That is, if the company is changing addresses (which is rare in real life), then one can take the same approach as with the contact persons. But if the company is actually maintaining more than one address, like it has several branches, then a simple query joining two tables will work.

CREATE TABLE company_address(
	id char(36) NOT NULL,
	address text NULL,
	company_id char(36) NOT NULL,
	PRIMARY KEY (id)
)ENGINE=InnoDb;

INSERT INTO company_address SET id = UUID(),
	address = 'Somewhere Ave.',
	company_id = (SELECT id FROM company WHERE acct_no = '100');
INSERT INTO company_address SET id = UUID(),
	address = 'Somewhere Else Ave.',
	company_id = (SELECT id FROM company WHERE acct_no = '100');
INSERT INTO company_address SET id = UUID(),
	address = 'Some Place',
	company_id = (SELECT id FROM company WHERE acct_no = '101');

SELECT company, address FROM company 
	LEFT JOIN company_address ON company.id = company_address.company_id
	ORDER BY company;

With the last query giving the following output:

	
+-----------------+---------------------+
| company         | address             |
+-----------------+---------------------+
| ABC Corporation | Somewhere Ave.      |
| ABC Corporation | Somewhere Else Ave. |
| XYZ Enterprises | Some Place          |
+-----------------+---------------------+

Conclusion

As to how transactions can be associated with companies, that is an entirely new set of tasks. Hopefully this blog is of some help to the reader.


...
Totoy Mola
When I look at the universe and all the ways the universe wants to kill us, I find it hard to reconcile that with statements of beneficence.
...
Ben Yalung
When I look at the universe and all the ways the universe wants to kill us, I find it hard to reconcile that with statements of beneficence.