Sometimes it's OK to break the rules.

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_iditem_name
1Vermeer
2Rubens
3Michelangelo
Art
item_iditem_nameartist_item_id
1The Milkmaid1
2Hippopotamus Hunt2
3David3
4Sistine Chapel ceiling3
5Pieta3

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_iditem_nameartist_item_idartist_item_name
1The Milkmaid1Vermeer
2Hippopotamus Hunt2Rubens
3David3Michelangelo
4Sistine Chapel ceiling3Michelangelo
5Pieta3Michelangelo
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.

Recent Articles

Graphic design ≠ UI design.
Bad user experience can't be offset with pretty pictures.

You don’t know what you don’t know.
The time to involve experienced personnel is before you make a mistake.

Your success is tied to how you work the web (pt 2).
A website can’t succeed without effective search engine optimization.

Your success is tied to how you work the web (pt 1).
Do your own web assessment of succeeding and failing companies.

Why does everything have to be so difficult?
Some things feel broken even though they work.

Worst of the Web
Hey everybody! It’s rant-day again!

Sell spoons for a living, eat soup with a fork (pt 2).
Use your agency’s website as an example of how you work.

Sell spoons for a living, eat soup with a fork (pt 1).
Remember to give yourself the full client treatment.

I’ll let you fly the plane. You let me build the website.
Do what you do, and don’t do what you don’t do.

Worst of the Web
This is less like useful insight and more like a rant.

Our customers say it best

What sets Dialogs apart is not just their technical prowess but their strategic insight. They don’t merely fulfill our technology requirements; they actively contribute to our decision-making process, guiding us towards the most efficient technological solutions. 

Kathleen B