I am re-designing our company database, and am wondering whether or not to split this table up into two (or even three) different tables based on the type
field. Prospects can become clients, and in rare cases, clients can revert to being prospects again (usually correction of a user error). Currently there are no suppliers who are clients, but theoretically there's nothing to stop that from happening in the future. As you can see, several fields are only needed in one case or another.
CREATE TABLE `companies` (
/* book-keeping fields every data table should have */
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`created` TIMESTAMP NULL DEFAULT NULL, /* would love to specify DEFAULT CURRENT_TIMESTAMP here but MySQL won't let me */
`modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* fields for all company types */
`type` ENUM('prospect','client','supplier') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'prospect',
`name` VARCHAR(25) DEFAULT NULL,
`abbr_name` VARCHAR(8) DEFAULT NULL,
`formal_name` VARCHAR(255) DEFAULT NULL,
`web_site` VARCHAR(255) DEFAULT NULL,
`email` VARCHAR(320) DEFAULT NULL,
`logo` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`key` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
`bust` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
`head_office` INT UNSIGNED DEFAULT NULL,
/* only used for clients */
`accounting_code` VARCHAR(5) CHARACTER SET ascii DEFAULT NULL,
/* used for prospects, and clients obtained by marketing */
`marketing_user` INT UNSIGNED DEFAULT NULL,
`marketing_datasource` CHAR(2) CHARACTER SET ascii DEFAULT NULL,
/* only used for prospects */
`marketing_dead` ENUM('false','true') CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'false',
/* only used for suppliers */
`supplies` SET('materials','plant') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`account_holding_branch` INT UNSIGNED DEFAULT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`head_office`) REFERENCES sites (`id`),
FOREIGN KEY (`marketing_user`) REFERENCES users (`id`),
FOREIGN KEY (`account_holding_branch`) REFERENCES sites (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
What do people recommend, and importantly, why?
A similar question, Splitting a table based on business logic provides one curt answer with two relevant reasons to help decide whether splitting is worth it:
- Partition hot and cold data for cache efficiency
- Many columns almost always null
As to data 'temperature', key
clients and key
suppliers are hotter than other clients or suppliers, and hotter than prospects. Any company which has gone bust
or for which the marketing trail has died
are pretty frigid.
As to NULL
, it varies, but prospects almost always do not have a logo uploaded, and suppliers presently do not have data in any of the four accounting_code
and marketing_*
fields, though as I say there's no technical reason why a parts supplier could not also become a client of our business.
created
column and the comment there. You can do this in MySQL 5.6