niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » MySQL
Normailizing SQL Result Set
Post new topic   Reply to topic Page 1 of 1 [3 Posts] View previous topic :: View next topic
Author Message
Michael Caplan
*nix forums beginner


Joined: 21 Jun 2005
Posts: 7

PostPosted: Wed Jul 19, 2006 7:56 pm    Post subject: Re: Normailizing SQL Result Set Reply with quote
I hate responding to my self on a list, but I don't think I outlined the
problem clearly enough:

The article table data looks like this:

select * from articles;
+----+--------+
| id | active |
+----+--------+
| 1 | 1 |
+----+--------+


and the articles_attributes table looks like this:

select * from articles_attribs;
+------------+--------+-------------+---------------------------------+
| article_id | locale | attrib_key | attrib_value |
+------------+--------+-------------+---------------------------------+
| 1 | en_CA | content | <h1>Some Content</h1> |
| 1 | en_CA | description | This is the article description |
| 1 | en_CA | keyword | These are, article, keywords |
| 1 | en_CA | title | Test article |
+------------+--------+-------------+---------------------------------+


If I want to select all articles, my first thought was to do this:

SELECT a.id, a.active, b.* FROM articles as a LEFT JOIN articles_attribs
as b ON a.id = b.article_id

however, this results in an "un-normalized" result set:

+----+--------+------------+--------+-------------+---------------------------------+
| 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 |
+----+--------+------------+--------+-------------+---------------------------------+


As you can see, I have 4 rows for article of ID one. One row for each
article attribute.

What I want to have returned to me would look something like this (I
really want to avoid programmatically cleaning up the result set):


+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| id | active | content | title |
description | keyword |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| 1 | 1 | <h1>Some Content</h1> | Test article | This is the
article description | These are, article, keywords |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+



The closets I have come to is this beastly SQL:

SELECT
a.id,
a.active,
a1.attrib_value as `content`,
a2.attrib_value as `title`,
a3.attrib_value as `description`,
a4.attrib_value as `keyword`
FROM articles as a
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'content') as a1
ON
a.id = a1.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'title') as a2
ON
a.id = a2.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'description') as a3
ON
a.id = a3.article_id
LEFT JOIN
(SELECT
article_id,
attrib_value
FROM
articles_attribs
WHERE
attrib_key = 'keyword') as a4
ON
a.id = a4.article_id;



which results in what I am looking for:

Code:

+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| id | active | content | title |
description | keyword |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+
| 1 | 1 | <h1>Some Content</h1> | Test article | This is the
article description | These are, article, keywords |
+----+--------+-----------------------+--------------+---------------------------------+------------------------------+


However, these subqueries look like they will be wildly inefficient.
What I gather the DB (MySQL) is doing here is getting _all_ attributes
of type X for all articles, and then joining that result set together
with article ID of the current row.


Any ideas?

Michael Caplan wrote:
Quote:
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:


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: michael@apison.com
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/mysql?unsub=gcdmg-mysql@m.gmane.org
Back to top
Peter Lauri
*nix forums beginner


Joined: 25 Nov 2005
Posts: 40

PostPosted: Wed Jul 19, 2006 5:54 pm    Post subject: RE: Normailizing SQL Result Set Reply with quote
This is my ugly solution:

SELECT
MAX(IF(articles_attribs.attrib_key='content', articles_attribs.
attrib_value, '')) AS content,
MAX(IF(articles_attribs.attrib_key='description', articles_attribs.
attrib_value, '')) AS description,
MAX(IF(articles_attribs.attrib_key='keyword', articles_attribs.
attrib_value, '')) AS keyword,
MAX(IF(articles_attribs.attrib_key='title', articles_attribs. attrib_value,
'')) AS title,
FROM articles
LEFT OUTER JOIN articles_attribs ON
(articles_attribs.article_id=articles.id)
WHERE articles.id=1

But I do not really understand why this would be "normalized", it is a ugly
and not so dynamic solution.

/Peter

-----Original Message-----
From: Michael Caplan [mailto:michael@apison.com]
Sent: Thursday, July 20, 2006 12:54 AM
To: mysql@lists.mysql.com
Subject: Normailizing SQL Result Set

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:


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: michael@apison.com
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/mysql?unsub=lists@dwsasia.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org
Back to top
Michael Caplan
*nix forums beginner


Joined: 21 Jun 2005
Posts: 7

PostPosted: Wed Jul 19, 2006 5:54 pm    Post subject: Normailizing SQL Result Set Reply with quote
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:


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: michael@apison.com
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/mysql?unsub=gcdmg-mysql@m.gmane.org
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [3 Posts] View previous topic :: View next topic
The time now is Wed Dec 03, 2008 8:28 pm | All times are GMT
navigation Forum index » Databases » MySQL
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Creating relational view for an ODBC result set? antilog@gmail.com Server 0 Fri Jul 21, 2006 5:56 am
No new posts Pivoting the result set. Ow Mun Heng MySQL 1 Sun Jul 16, 2006 4:00 am
No new posts Search page result links Chris PHP 0 Thu Jul 06, 2006 6:45 pm
No new posts fastcgi: how to accept HTTP requests, and return a result thorley@gmail.com python 0 Thu Jul 06, 2006 3:55 pm
No new posts Newbie: How to display query result in table? Huevos PHP 9 Thu Jul 06, 2006 4:36 am

Property in Spain | Credit Score | Personalized Gifts | C340 Garmin GPS | Loan
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1694s ][ Queries: 20 (0.0501s) ][ GZIP on - Debug on ]