I got another article published. In fact, it looks like I made the cover of the June 2008 issue of php|architect. The article is titled "EAV Modeling" and talks about a database design I've used in a couple of recent projects.
Working with the editor on this assignment was a good experience. She made me feel a lot more involved in the process than I did with the Linux Journal article. *shrug*
5 comments:
Hi Carl,
thanks for that great article in php-archtitect. It really helped me! I am just working with Magento and it gave me the opportunity to understand how it works. I just have one question to your article in Listing 10. You split up the values so that varchars are stored in the Table Varchar, text in text ... how can i make my Join Query (When selecting the data) understand that sometimes it has to join with the varchar table sometimes with the text table... When insering data its easy as you described but when selecting with a join statement i have no idea how to build in this switch.
Thanks
Best Regards
Chris
Hi, Chris. Thanks for writing, and I'm glad that you liked the article.
You can download the code for that article at www.phparch.com/code/, and the listing6.php file (which differs from the "Listing 6" appearing in the article) contains an SQL statement you can use to retrieve the data. It's a union which grabs the data from each value table in turn.
Hi Carl,
I just read about your article via a blogpost from Matthew Turland.
I'm currently working on a content storage project to combine the advantages of EAV with advantages of having plain database tables. I'd be glad to get your comments! There is also an earlier blogpost with the ERM Diagram of my idea.
I'll buy and read your phparch article after my next math exam!
Hi Carl,
I just finished reading your article on EAV in php|architect. Oddly it coincides with a project I'm currently working on where I used a very similar approach, not knowing there was a model for it. :-)
I had a question about your decision to make the unique constraints on the values fields. To continue your example, if Carl Smith and Carl Jones both submit applications, there will be only one "Carl" in the first name. If Carl Smith later decides to delete his application and cascading deletes are in force, wouldn't Carl Jones lose his first name? I haven't studied MySQL cascades yet but wondered if it kept reference counts across relationships, similar to managing references to objects.
Enjoyed your bits about the barn swallows too...we had a mockingbird who raised chicks outside our bedroom window for a decade before I finally had to board up the hole into the attic when we sold the house. One of their calls sounded suspiciously like our cat. :-)
Tim
Tim, thanks for leaving a comment. Yes, it was fun having the barn swallows as neighbors. I still see them from time to time.
No, once a something goes into a value table, it stays there. The cascading delete would take effect if you deleted a row out of the entity table ("applications" in the article)--it would delete all corresponding rows from the linking table ("application_data"). In fact, if you tried deleting a value table row which had at least one corresponding row in the linking table, the "ON DELETE RESTRICT" clause should prevent that value table deletion.
Post a Comment