So I have a relatively complex database I'm designing - basically full in-house accounting setup. In one section I have some concern that I'm over-normalizing; the 'directory' - I'll example the employee section but there will be the same basic thing for customers, suppliers etc.
Basically directory
stores information that is relevant to all types whether customer or employee or x, employees can have any number of addresses, email addresses etc.
Using MySQL 5.6.12, PHP/PDO (not that what I'm accessing it from really matters for the question)
The relevant table creation calls:
CREATE TABLE IF NOT EXISTS `directory` (
`directory_id` int(11) NOT NULL AUTO_INCREMENT,
`name_display` varchar(255) NOT NULL,
`type` tinyint(2) unsigned NOT NULL COMMENT '0: human, 1: unincorporated, 2: incorporated',
`visibility` varchar(255) NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`directory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `directory_addresses` (
`directory_id` int(11) NOT NULL,
`address_id` int(11) NOT NULL AUTO_INCREMENT,
`type` int(11) NOT NULL,
`country` int(11) NOT NULL,
`region` int(11) NOT NULL,
`city` varchar(255) NOT NULL,
`postalcode` varchar(10) NOT NULL,
`line1` varchar(255) NOT NULL,
`line2` varchar(255) NOT NULL,
`version` int(11) NOT NULL,
`primary` tinyint(1) NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`address_id`),
KEY `fk_type` (`type`),
KEY `fk_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `directory_email` (
`directory_id` int(11) NOT NULL,
`email_id` int(11) NOT NULL AUTO_INCREMENT,
`primary` tinyint(1) NOT NULL,
`email` varchar(255) NOT NULL,
PRIMARY KEY (`email_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `directory_employee` (
`directory_id` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`employee_id` varchar(10) NOT NULL,
`start_date` date NOT NULL,
`end_date` date DEFAULT NULL,
`department` int(11) NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `directory_employee_payrate` (
`employee_id` int(11) NOT NULL,
`payrate_id` int(11) NOT NULL AUTO_INCREMENT,
`start_date` date NOT NULL,
`end_date` date DEFAULT NULL,
`rate` decimal(10,2) NOT NULL,
PRIMARY KEY (`payrate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin
Which for the employee list overview page results in a query of:
Select
directory_employee.directory_id,
directory_employee.employee_id,
directory_employee.department,
directory_employee.start_date,
directory_employee.end_date,
directory_employee.notes,
directory_employee_payrate.rate,
directory.name_display,
directory_email.email,
directory_phone.phone,
directory_addresses.line1,
directory_addresses.line2,
directory_addresses.city,
directory_addresses.region,
directory_addresses.postalcode,
directory_addresses.country
FROM directory_employee
LEFT JOIN directory_employee_payrate ON
directory_employee_payrate.employee_id = directory_employee.id
AND directory_employee_payrate.end_date IS NULL
LEFT JOIN directory ON
directory.directory_id = directory_employee.directory_id
LEFT JOIN directory_addresses ON
directory_addresses.directory_id = directory_employee.directory_id
AND directory_addresses.primary = 1
LEFT JOIN directory_email ON
directory_email.directory_id = directory_employee.directory_id
AND directory_email.primary = 1
LEFT JOIN directory_phone ON
directory_phone.directory_id = directory_employee.directory_id
AND directory_phone.primary = 1
Which is admittedly somewhat LONG!
So my question(s) basically boils down to:
- Does that look like a reasonable schema?
- Is it over-normalized? (If so what would you recommend doing instead - serialized arrays?)
- If that is a reasonable schema, are there any optimizations that would be a good
idea to apply to that
Select
statement? - Any schema improvements that you'd recommend outside of the main point of this question? (I do have FK - I just haven't set them all up/copied the definition statements.)