Hi there,
I am trying to work through a DB design that is giving me some trouble with the
result sets. The situation is this:
I have a table called "articles" and a related table call "article_attributes" Within the table
"articles" I am maintaining basic info about an article, such as article id, active, etc. However, I am not
maintaining any lanuage specific data about the article in that table (title, description, body, etc). All that info
is stored in "articles_attributes". The goal of storing all language specific info about an article in a
seperate table is two fold: [CODE][/CODE]
1) I wish to maintain multiple language versions of an article without prior
knowedge to the languages I have to handle,
2) I also wish to be able to easly add arbitrary new article attributes (eg:
footnotes) without needing the modify the db tables.
This is what I came up with for the structure:
CREATE TABLE `articles` (
`id` int(10) unsigned NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `active_idx_idx` (`active`)
)
CREATE TABLE `articles_attribs` (
`article_id` int(10) unsigned NOT NULL default '0',
`locale` varchar(5) NOT NULL default ' ',
`attrib_key` varchar(255) NOT NULL default ' ',
`attrib_value` longtext NOT NULL,
PRIMARY KEY (`article_id`,`locale`,`attrib_key`),
KEY `values_idx_idx` (`attrib_value`(767))
)
This works okay, however, when querying the database for one record, because of
the join between articles => articles_attributes, I don't get one result set,
but rather 1 X the number of attributes recorded for the article:
+----+--------+------------+--------+-------------+---------------------------------+
| id | active | article_id | locale | attrib_key | attrib_value
|
+----+--------+------------+--------+-------------+---------------------------------+
| 1 | 1 | 1 | en_CA | content | <h1>Some Content</h1>
|
| 1 | 1 | 1 | en_CA | description | This is the article
description |
| 1 | 1 | 1 | en_CA | keyword | These are, article,
keywords |
| 1 | 1 | 1 | en_CA | title | Test article
|
+----+--------+------------+--------+-------------+---------------------------------+
What I am struggling with is an elegant way I can normalize the result set. I
want to end up with a result set that looks like this:
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
| id | active | article_id | locale | content | description
| keyword | title |
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
| 1 | 1 | 1 | en_CA | <h1>Some Content</h1> | This is the
article description | These are, article, keywords | Test article |
+----+--------+------------+--------+-----------------------+---------------------------------+------------------------------+--------------+
I can programmatically go through the result set and flatten it so that all
attrib_key values => corresponding attrib_value, but this is less than ideal.
I'm wondering if any of you have ideas how I can acheive the desired result with
some creative SQL?
Thanks,
Michael
--
Michael Caplan - Zend Certified PHP Engineer
Programming Manager
Apison Communications
Suite 110, 151 Provost Street
New Glasgow, NS, Canada B2H 2P6
Phone: (902) 695-3375
Toll Free: (800) 845-6998
Fax: (902) 695-7777
email: [EMAIL PROTECTED]
URL: http://www.apison.com
Specializing in web development, graphic design and Internet marketing
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
