6
\$\begingroup\$

I am designing a database and I am just wondering if I am doing it correctly. I have tables for things like State with all the states in them and then I reference the ID in other tables. But should I just handle this in my UI and then store it as a string or is this the correct way?

The goal of this database is to handle day to day operations of a transportation company. This version of the database is only focused on handling People and contacts. We manage employees, drivers, and there positions in the company. We will be tracking things like accidents, and other incidents drivers have, and gain a better understanding of driver / employee turn over.

--
-- Database: `hrm`
--

-- --------------------------------------------------------

--
-- Table structure for table `address`
--

CREATE TABLE `address` (
  `address_id` int(11) NOT NULL COMMENT 'Primary key for address rows.',
  `address_line_one` varchar(60) NOT NULL COMMENT 'First street-address line.',
  `address_line_two` varchar(60) DEFAULT NULL COMMENT 'Second street address line.',
  `city` varchar(30) NOT NULL COMMENT 'Name of the city.',
  `state_id` int(11) NOT NULL COMMENT 'Foreign key to state table.',
  `postal_code` varchar(15) NOT NULL COMMENT 'Postal code for the street address.',
  `modified_date` datetime NOT NULL COMMENT 'Date and time the row was last updated.'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `contact`
--

CREATE TABLE `contact` (
  `contact_id` int(11) NOT NULL COMMENT 'Primary key for contact rows.',
  `title` varchar(8) DEFAULT NULL COMMENT 'A courtesy title. For example, Mr. or Ms.',
  `first_name` varchar(50) NOT NULL COMMENT 'First name of the person.',
  `middle_name` varchar(50) DEFAULT NULL COMMENT 'Middle name or middle initial of the person.',
  `last_name` varchar(50) NOT NULL COMMENT 'Last name of the person.',
  `suffix` varchar(10) DEFAULT NULL COMMENT 'Surname suffix. For example, Sr. or Jr.',
  `email_address` varchar(50) DEFAULT NULL COMMENT 'E-mail address for the person.',
  `phone_number` varchar(25) DEFAULT NULL COMMENT 'Phone number associated with the person.',
  `address_id` int(11) DEFAULT NULL COMMENT 'Foreign key to address table. ',
  `modified_date` datetime NOT NULL COMMENT 'Date and time the row was last updated.'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT=' ';

-- --------------------------------------------------------

--
-- Table structure for table `driver`
--

CREATE TABLE `driver` (
  `driver_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `driver_type_id` int(11) NOT NULL,
  `rating_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `driver_type`
--

CREATE TABLE `driver_type` (
  `driver_type_id` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `abbreviation` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `driver_type`
--

INSERT INTO `driver_type` (`driver_type_id`, `name`, `abbreviation`) VALUES
(1, 'Ambulatory', 'AMB'),
(2, 'Wheelchair', 'WC'),
(3, 'Bus', 'BUS'),
(4, 'Taxi', 'TX');

-- --------------------------------------------------------

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `employee_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `job_title` varchar(50) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `interview_date` date DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `modified_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`employee_id`, `contact_id`, `status_id`, `job_title`, `birth_date`, `interview_date`, `hire_date`, `modified_date`) VALUES
(1, 1, 2, 'IT Director', '1991-08-10', '2016-02-17', '2016-02-18', '2016-04-25 11:58:00');

-- --------------------------------------------------------

--
-- Table structure for table `incident`
--

CREATE TABLE `incident` (
  `incident_id` int(11) NOT NULL,
  `incident_type_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `reported_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `incident_type`
--

CREATE TABLE `incident_type` (
  `incident_type_id` int(11) NOT NULL,
  `name` varchar(75) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `incident_type`
--

INSERT INTO `incident_type` (`incident_type_id`, `name`) VALUES
(1, 'Tardiness'),
(2, 'No Show'),
(3, 'Insubordination'),
(4, 'Failure to follow protocol'),
(5, 'Failure to report incident'),
(6, 'Dress code violation');

-- --------------------------------------------------------

--
-- Table structure for table `note`
--

CREATE TABLE `note` (
  `note_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  `note` varchar(255) NOT NULL,
  `created_date` datetime NOT NULL,
  `modified_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `rating`
--

CREATE TABLE `rating` (
  `rating_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `rating`
--

INSERT INTO `rating` (`rating_id`, `name`) VALUES
(1, 'Very Poor'),
(2, 'Poor'),
(3, 'Fair'),
(4, 'Good'),
(5, 'Very Good');

-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE `state` (
  `state_id` int(11) NOT NULL COMMENT 'Primary key for state table.',
  `name` varchar(50) NOT NULL COMMENT 'The name of the state.',
  `abbreviation` varchar(5) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `state`
--

INSERT INTO `state` (`state_id`, `name`, `abbreviation`) VALUES
(1, 'Alabama', 'AL'),
(2, 'Alaska', 'AK'),
(3, 'Arizona', 'AZ'),
(4, 'Arkansas', 'AR'),
(5, 'California', 'CA'),
(6, 'Colorado', 'CO'),
(7, 'Connecticut', 'CT'),
(8, 'Delaware', 'DE'),
(9, 'Florida', 'FL'),
(10, 'Georgia', 'GA'),
(11, 'Hawaii', 'HI'),
(12, 'Idaho', 'ID'),
(13, 'Illinois', 'IL'),
(14, 'Indiana', 'IN'),
(15, 'Iowa', 'IA'),
(16, 'Kansas', 'KS'),
(17, 'Kentucky', 'KY'),
(18, 'Louisiana', 'LA'),
(19, 'Maine', 'ME'),
(20, 'Maryland', 'MD'),
(21, 'Massachusetts', 'MA'),
(22, 'Michigan', 'MI'),
(23, 'Minnesota', 'MN'),
(24, 'Mississippi', 'MS'),
(25, 'Missouri', 'MO'),
(26, 'Montana', 'MT'),
(27, 'Nebraska', 'NE'),
(28, 'Nevada', 'NV'),
(29, 'New Hampshire', 'NH'),
(30, 'New Jersey', 'NJ'),
(31, 'New Mexico', 'NM'),
(32, 'New York', 'NY'),
(33, 'North Carolina', 'NC'),
(34, 'North Dakota', 'ND'),
(35, 'Ohio', 'OH'),
(36, 'Oklahoma', 'OK'),
(37, 'Oregon', 'OR'),
(38, 'Pennsylvania', 'PA'),
(39, 'Rhode Island', 'RI'),
(40, 'South Carolina', 'SC'),
(41, 'South Dakota', 'SD'),
(42, 'Tennessee', 'TN'),
(43, 'Texas', 'TX'),
(44, 'Utah', 'UT'),
(45, 'Vermont', 'VT'),
(46, 'Virginia', 'VA'),
(47, 'Washington', 'WA'),
(48, 'West Virginia', 'WV'),
(49, 'Wisconsin', 'WI'),
(50, 'Wyominng', 'WY');

-- --------------------------------------------------------

--
-- Table structure for table `status`
--

CREATE TABLE `status` (
  `status_id` int(11) NOT NULL,
  `name` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `status`
--

INSERT INTO `status` (`status_id`, `name`) VALUES
(1, 'Applied'),
(2, 'Employed'),
(3, 'Disabled'),
(4, 'Incarcerated'),
(5, 'Deceased'),
(6, 'Quit'),
(7, 'Terminated'),
(8, 'Incompatible');

-- --------------------------------------------------------

--
-- Table structure for table `upload`
--

CREATE TABLE `upload` (
  `upload_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `size` varchar(200) NOT NULL,
  `type` varchar(200) NOT NULL,
  `url` varchar(200) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `upload_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `user_id` int(11) NOT NULL,
  `contact_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `last_login` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `address`
--
ALTER TABLE `address`
  ADD PRIMARY KEY (`address_id`),
  ADD KEY `state_id` (`state_id`);

--
-- Indexes for table `contact`
--
ALTER TABLE `contact`
  ADD PRIMARY KEY (`contact_id`),
  ADD KEY `address_id` (`address_id`);

--
-- Indexes for table `driver`
--
ALTER TABLE `driver`
  ADD PRIMARY KEY (`driver_id`),
  ADD KEY `employee_id` (`employee_id`),
  ADD KEY `driver_type_id` (`driver_type_id`),
  ADD KEY `rating_id` (`rating_id`);

--
-- Indexes for table `driver_type`
--
ALTER TABLE `driver_type`
  ADD PRIMARY KEY (`driver_type_id`);

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
  ADD PRIMARY KEY (`employee_id`),
  ADD UNIQUE KEY `ix_contact_id` (`contact_id`),
  ADD KEY `idx_status_id` (`status_id`);

--
-- Indexes for table `incident`
--
ALTER TABLE `incident`
  ADD PRIMARY KEY (`incident_id`),
  ADD KEY `incident_type_id` (`incident_type_id`),
  ADD KEY `driver_id` (`driver_id`);

--
-- Indexes for table `incident_type`
--
ALTER TABLE `incident_type`
  ADD PRIMARY KEY (`incident_type_id`);

--
-- Indexes for table `note`
--
ALTER TABLE `note`
  ADD PRIMARY KEY (`note_id`),
  ADD KEY `contact_id` (`contact_id`);

--
-- Indexes for table `rating`
--
ALTER TABLE `rating`
  ADD PRIMARY KEY (`rating_id`);

--
-- Indexes for table `state`
--
ALTER TABLE `state`
  ADD PRIMARY KEY (`state_id`);

--
-- Indexes for table `status`
--
ALTER TABLE `status`
  ADD PRIMARY KEY (`status_id`);

--
-- Indexes for table `upload`
--
ALTER TABLE `upload`
  ADD PRIMARY KEY (`upload_id`),
  ADD KEY `contact_id` (`contact_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `contact_id` (`contact_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `address`
--
ALTER TABLE `address`
  MODIFY `address_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key for address rows.', AUTO_INCREMENT=22;
--
-- AUTO_INCREMENT for table `contact`
--
ALTER TABLE `contact`
  MODIFY `contact_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key for contact rows.', AUTO_INCREMENT=23;
--
-- AUTO_INCREMENT for table `driver`
--
ALTER TABLE `driver`
  MODIFY `driver_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `driver_type`
--
ALTER TABLE `driver_type`
  MODIFY `driver_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
  MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
--
-- AUTO_INCREMENT for table `incident`
--
ALTER TABLE `incident`
  MODIFY `incident_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `incident_type`
--
ALTER TABLE `incident_type`
  MODIFY `incident_type_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `note`
--
ALTER TABLE `note`
  MODIFY `note_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `rating`
--
ALTER TABLE `rating`
  MODIFY `rating_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `state`
--
ALTER TABLE `state`
  MODIFY `state_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key for state table.', AUTO_INCREMENT=51;
--
-- AUTO_INCREMENT for table `status`
--
ALTER TABLE `status`
  MODIFY `status_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `upload`
--
ALTER TABLE `upload`
  MODIFY `upload_id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Phpmyadmin designer view

\$\endgroup\$
0
2
\$\begingroup\$

latin1 in this day and age? You probably have a plan to deal with names that aren't in that character set? The whole split between first, middle and last name also might conflict with the real world. (I guess the names argument is a bit overkill, but you could possibly also get away with a single generic "name" field.)

A few tables are really hidden enumerations - you could think about using something on the application level rather than requiring yet another join on every operation (or use MySQL enumerations which are rather unwieldy though). Edit: That's basically the answer to your first question, i.e. I'd probably go with the actual string value if you don't need to associate more information with the enumeration.

The user table has the column password. That has to be a password hash (meaning the field will have a lower fixed length) and should consequently be named password_hash or something similar.

Lacking the exact requirements I still find the upload table a bit weird - why are size and type of type varchar(200)? Both sound way more restricted in scope and size more like it should be an integer type instead.

\$\endgroup\$
1
\$\begingroup\$

Being non-american I directly notice that address requires a state, and doesnt have a country code. Of course if the application can never ever need foreign addresses, and only should accept "complete" addresses that may be ok. Also, if you DO add countries, each country can have its own set of states.

\$\endgroup\$
1
  • \$\begingroup\$ Thank you for your comment. I did consider this but my company only operates in the united states so country code is not needed. \$\endgroup\$ May 13 '16 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.