2
\$\begingroup\$

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, 'j.smith@example.com', '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?

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

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.
\$\endgroup\$
  • \$\begingroup\$ So are you basically saying (without going into the complex approach) to keep the structre as it is - aside from splitting out city, postcode and county from address - and separate the class into object and mapper? \$\endgroup\$ – Carl Porter Feb 23 '14 at 19:44
  • \$\begingroup\$ I was taught that a table name should reflect what a single record represents. So it's Person because it represents one person, where as PersonDates would reflect multiple dates – dob & dod – so it should be plural. It's worked for me so I've stuck with it. I do use foreign keys, but I've had problems deleting tables via phpMyAdmin once foreign keys are defined, so I tend to wait until I'm certain on my structure. \$\endgroup\$ – Carl Porter Feb 23 '14 at 19:45
  • \$\begingroup\$ Yes, it is a solid approach. Split these fields on a database only level though. Splitting them on the model level would introduce a new City entity :). About the naming: its something personal really. MySql documentation uses plural too. Imho it really doesn't matter what you do, just stick to it - that's the real important thing. \$\endgroup\$ – Fge Feb 23 '14 at 20:04
  • \$\begingroup\$ Thanks. So with using mappers, would I still do $person->save() - where Person just calls the save method of PersonMapper or would I have do PersonMapper::save($person)? I did a quick search for 'PHP Mapper' and 'PHP ORM' but all I seem to get is links for software that uses this approach. \$\endgroup\$ – Carl Porter Feb 23 '14 at 20:26
  • \$\begingroup\$ I'd be Repository::save($person). The mapper is not responsible for this. It is only responsible for for mapping Database <-> Model. The mapper can be part of of the repository (either fully integrated, slightly violating single responsibility, or as an extra class passed to the repository at construction time). As a rule of thumb: the model interacts with the repository only, the mapper is called by the repository only, the repository calls the database as only class. \$\endgroup\$ – Fge Feb 23 '14 at 20:37

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.