Sometimes it's OK to break the rules.

– Charlie Brown 12 January 2010

Website content in Dialogs is defined by information structures we call “Lists”. Each List corresponds to a MySQL table within the Dialogs database. Dialogs presents a simple interface to the developer for List creation and maintenance so developers don’t have to directly interface with MySQL unless they want to. For those more technical developers wanting to understand how to optimize content maintenance and website performance, some basic SQL skills and some insight into how Dialogs List Templates are used to map database fields into rendered pages goes a long way.

It is quite common for data in one Dialogs List to be related to another. Imagine a List of Artists and a companion List of the art they’ve created. Relational database theory defines a certain way to manage that interrelation according to something called third-normal-form (3NF). Among other things, 3NF states that no information should be duplicated in both tables. Is that really a rule that shouldn't be broken? In the days of expensive storage and crude data handling tools, it sure made sense. Adhering to 3NF, however, makes for unnecessarily complex SQL within Dialogs List Templates, something that for efficient development and ongoing maintenance we'd like to avoid. Here's an example to illustrate the problem. Lets say you have these two tables:

Artist
item_id	item_name
1	Vermeer
2	Rubens
3	Michelangelo

Art
item_id	item_name	artist_item_id
1	The Milkmaid	1
2	Hippopotamus Hunt	2
3	David	3
4	Sistine Chapel ceiling	3
5	Pieta	3

The relationship between the tables is obvious. The 'artist_item_id' field 'links' to the item_id of the 'artist' table.

If you want to display the art that's easy:

SELECT * FROM art

... but if you want to include the name of the artist it's this mess:

SELECT art.*,artist.item_name AS artist_item_name 
FROM art LEFT JOIN artist ON art.artist_item_id=artist.item_id 
WHERE artist.item_id IS NOT NULL

Yuck!! And it's worse than that. If you want to specify a sort you can't just say

ORDER BY item_name

you have to remember to prefix the table name and the same goes for anything in the 'WHERE' clause as well.

It's time to go off the reservation. If we break from the 3NF rules and add the artist's name to the art table we would get this:

Art
item_id	item_name	artist_item_id	artist_item_name
1	The Milkmaid	1	Vermeer
2	Hippopotamus Hunt	2	Rubens
3	David	3	Michelangelo
4	Sistine Chapel ceiling	3	Michelangelo
5	Pieta	3	Michelangelo

Super! Now we're back to

SELECT * FROM art

... and we have the artist's name! The only problem is, if Michelangelo's name is misspelled, and someone changes it in the 'artist' table, it will still be wrong in the 'art' table. This is where Dialogs shines. All we have to do is add one line to the list automation script which gets run for every edit to a list item. This one line will update the art table and keep the artist's names in sync with the 'artist' table:

$this->query("UPDATE art LEFT JOIN artist ON art.artist_item_id=artist.item_id
  SET art.artist_item_name=artist.item_name");

Cons:

1. Causes baldness in database purists.
2. Wastes hard drive space.

Pros:

1. Easy to implement: add one more field to the list, add one line to the automation script.
2. Dead simple list templates because all the fields you need are there.
3. CSV and XML exports have all the fields you need too.
4. Hard drive space is beyond cheap.

Dialogs is a real-world development tool for the professional web developer. We developed Dialogs with the belief that successful deployments either follow best-practices or define them. So go ahead, break the rules. We won't tell your database theory professor. For more detailed description of how to do this in Dialogs see the Knowledge Base Article.