Best structure for a Person in PHP classes (and databse)

Question

I am (in the process of) creating a system to store People and their details - names, date of birth (dob), addresses, phone numbers, etc. - and I'm curious how it is best achieved. Below is an an example of some of the SQL code with test data (the PHP classes match the structure in the db):

CREATE TABLE IF NOT EXISTS `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(16) DEFAULT NULL,
  `forename` varchar(50) DEFAULT NULL,
  `middlenames` varchar(50) DEFAULT NULL,
  `surname` varchar(50) DEFAULT NULL,
  `display` varchar(100) NOT NULL,
  `dob` datetime DEFAULT NULL,
  `dod` datetime DEFAULT NULL,
  `numViews` int(4) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `person` (`id`, `title`, `forename`, `middlenames`, `surname`, `display`, `dob`, `dod`, `views`) VALUES
(1, 'Miss', 'Julie', 'Nicola Louise', 'Smith', 'Mum', '1958-09-21 00:00:00', NULL, 1),
(2, 'Mr', 'David', NULL, 'Jones', 'Dave', '1932-06-19 00:00:00', NULL, 0);


CREATE TABLE IF NOT EXISTS `email` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `contactId` int(10) unsigned DEFAULT NULL,
  `address` varchar(250) DEFAULT NULL,
  `type` enum('Personal','Work') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `email` (`id`, `contactId`, `address`, `type`) VALUES
(1, 1, '[email protected]', 'Personal');


CREATE TABLE IF NOT EXISTS `address` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `line1` varchar(50) NOT NULL,
  `line2` varchar(50) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  `county` varchar(25) DEFAULT NULL,
  `postcode` varchar(8) DEFAULT NULL,
  `type` enum('Home','Office','Holiday','Other') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

INSERT INTO `address` (`id`, `line1`, `line2`, `city`, `county`, `postcode`, `type`) VALUES
(1, '32 Test Road', NULL, 'Some City', NULL, 'PO1 2ST', 'Home'),
(2, '6 High Street', 'New Road', 'Chatham', NULL, 'TN6 8BL', 'Home');


CREATE TABLE IF NOT EXISTS `personaddress` (
  `contactId` int(10) unsigned NOT NULL,
  `addressId` int(4) unsigned NOT NULL,
  PRIMARY KEY (`contactId`,`addressId`),
  KEY `addressId` (`addressId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `personaddress` (`contactId`, `addressId`) VALUES
(1, 1),
(2, 2);

I did move the dob & dod fields into another Personal table, as I realised that at times - such as showing email addresses for a Person - that it wasn't necessary to load the dob & dod fields. However, when I did this and when I then wanted to get People with upcoming birthdays, to display the person's name I either had to load a Person object at the same time or add the display field to the Personal class and join the tables in the SQL - which I really didn't want to do (loose coupling). So I ended up moving the fields back to Person.

So my question is what is the best way of implementing this structure?

Note that in a class I currently have standard getter/setter methods, then additional methods (such as getFullName, getFullLegalName, etc. for Person) and finally methods for db interaction – load, save, etc. I've also been informed that really I should put these other methods into different classes. So my second question (which I suppose is tied to the first really) is should I also have separate classes for different sets of methods?

Answer 1

First Question: Don't let potential use-cases of your application drive how you model the data. As you experienced, this causes a lot of headache later on.

Extracting the dob & dod for performance reasons is premature in this case. It is absolutely ok to load data even when not sure if it will be required. Every (un-optimized) application has a percentage of data it doesn't load. That's fine until you really experience performance issues. And then this probably is not the bottle neck you are going to optimize first. Loading those two extra values goes almost for free .

If you have larger data-structures (e.g. huge object tress by relations), you can use the lazy-loading pattern for this (which is what many ORM do to avoid joins). This keeps your model intact but avoids loading too much data. Lazy-loading scalars probably is more expensive then just loading them in the first place.

Excursus: What you tried to do is to model two different roles of the same entity. This is a valid approach some times. Yet this doesn't say the information is not loaded, it's just limiting the available interface. To be able to use its full power, generic functions are required though. An example might look like:

interface Person {
    // shared information
}

interface PersonName extends Person {
    // names
}

interface PersonDates extends Person {
    // dob & dob
}

public find<? extends Person>(int id) {
    return PersonImpl();
}

class PersonImpl implements PersonName, PersonDates {
}

repository.find<PersonName>(1);
repository.find<PersonDates>(1);

This is some very expensive pattern though, in terms of complexity and effort to create. You'll rarely use this. Especially as this doesn't make data loading faster. The entity is always loaded completely too.

Second Question: Well, you know my opinion here :). Separate your model from how it is stored. The repository (its mapper) should be the only one to know how to save the model to the database.

Database structure:

• I prefer to to name tables in plural (as they represent collections). Other do as you. Some kind a personal, stick to one convention though.
• Use InnoDb's foreign keys in your model. They help greatly to enforce data integrity.
• Normalization would require to extract city, postcode and county from your address table into a separate one.