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_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 NULLYuck!! And it's worse than that. If you want to specify a sort you can't just say
ORDER BY item_nameyou 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 |
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:
- Causes baldness in database purists.
- Wastes hard drive space.
- Easy to implement: add one more field to the list, add one line to the automation script.
- Dead simple list templates because all the fields you need are there.
- CSV and XML exports have all the fields you need too.
- 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
A mobile app that crashes can crash the developer.
For app developers, a bad first impression is hard to shake.
Why your business shouldn’t have an app.
If it doesn’t make you money, it’s a waste of money.
If I can’t own it, I don’t want it!
Ownership rights for websites are really no different than other communications.
Be careful what you say about your business.
It’s the things you overlook that say the most about who you are.
If you own a creative firm, listen up!
Opportunity is knocking like never before.
Get on about your business.
Seriously, 2011 is going to be a booming year.
Some decisions risk the life of your business.
Don't make the big decisions alone.
Acknowledging your limitations can build confidence.
Your clients do not expect you to know everything.
It's easier to form an opinion than to change one.
You need to actively pursue a great first impression.
The one thing you must have to succeed in business.
Simply having a website isn't enough any more.
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