MySQL Forums :: Database Design & Data Modelling :: Data model metadata design and data driven joins


Advanced Search

Data model metadata design and data driven joins
Posted by: Brian Kraft ()
Date: January 08, 2011 03:27AM

I have the requirement to build a dynamically assigned data attribute driven storage and retrieval structure. I want to handle as much of it as possible on the MySQL side and less on the application side.

I have multiple data profiles that have an arbitrary number of attributes assigned to the profile. Example being profile A has 20 entity_keys assigned to it who's value may be any one of 4 data storage types. The data points are assigned a name and a storage type and the associated value.

What I would like to do is a data driven join similar to http://www.artfulsoftware.com/infotree/queries.php#449
which I have been able to do successfully mapping the the class_id from the profile_map, Where I'm stuck is out to join the other two table's into the result.

The data_profile stores the ID's for the data models that can have intersecting entity_keys assigned to them. The data_profile_map holds the corresponding data maps to there storage table and there key names in the entity_keys in total join up to 7 tables of data together.

I would like to select the data_profile.data_id from the data_profile joining the data_profile_map on the data_id, join the entity_key_id to the entity_keys table to get the entity_value (the name of the value) and the corresponding value data from there storage tables meta_*

I have a functional query that works from the data_profile_map conditionally using the CASE method in http://www.artfulsoftware.com/infotree/queries.php#449 but it is returning BLOB data back on the values. I also can't seem to retrieve the entity_value or do this from the main data_profile table.

I've done this in the past but it was done mostly on the application side performing multiple single meta_value joins between the 4 tables, I'm hoping someone here has the wisdom to do this through a single query

working query of just data_profile_map to the meta tables that is conditional on the type_id, the type ID was in the entity_keys table but I as not able to successfully join them between the 3 tables
Select
data_profile_map.data_id,
data_profile_map.entity_type_id
CASE data_profile_map.type_id
WHEN 'meta_char' THEN m1.value
WHEN 'meta_int' THEN m2.value
WHEN 'meta_text' THEN m3.value
WHEN 'meta_varchar' THEN m4.value
WHEN 'meta_date' THEN m5.value
ELSE 'ERROR'
end as result
FROM data_profile_map
LEFT JOIN meta_char as m1 on data_profile_map.value_id = m1.value_id
LEFT JOIN meta_int as m2 on data_profile_map.value_id = m2.value_id
LEFT JOIN meta_text as m3 on data_profile_map.value_id = m3.value_id
LEFT JOIN meta_varchar as m4 on data_profile_map.value_id = m4.value_id
LEFT JOIN meta_date as m5 on data_profile_map.value_id = m5.value_id


table structures are below:

-- Server version: 5.1.41
-- PHP Version: 5.3.2-1ubuntu4.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `datamodel`
--

-- --------------------------------------------------------
--
-- Table structure for table `data_profile`
--

CREATE TABLE IF NOT EXISTS `data_profile` (
`data_id` int(11) unsigned NOT NULL,
`class_id` tinyint(2) unsigned NOT NULL,
`entity_key_id` int(6) unsigned NOT NULL,
KEY `data_id` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `data_profile_map`
--

CREATE TABLE IF NOT EXISTS `data_profile_map` (
`data_id` int(11) unsigned NOT NULL,
`type_id` tinyint(2) unsigned NOT NULL,
`value_id` int(6) unsigned NOT NULL,
`entity_key_id` int(6) unsigned NOT NULL,
KEY `data_id` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `entity_keys`
--

CREATE TABLE IF NOT EXISTS `entity_keys` (
`key_id` int(11) NOT NULL AUTO_INCREMENT,
`entity_value` varchar(255) NOT NULL,
PRIMARY KEY (`key_id`),
UNIQUE KEY `entity_value` (`entity_value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `meta_date`
--

CREATE TABLE IF NOT EXISTS `meta_date` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` date DEFAULT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `meta_int`
--

CREATE TABLE IF NOT EXISTS `meta_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) NOT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `meta_text`
--

CREATE TABLE IF NOT EXISTS `meta_text` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` text NOT NULL,
PRIMARY KEY (`value_id`),
KEY `value` (`value`(255))
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `meta_varchar`
--

CREATE TABLE IF NOT EXISTS `meta_varchar` (
`value_id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`value_id`),
UNIQUE KEY `value` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

Options: ReplyQuote


Subject Written By Posted
Data model metadata design and data driven joins Brian Kraft 01/08/2011 03:27AM
Re: Data model metadata design and data driven joins Rick James 01/09/2011 12:18PM
Re: Data model metadata design and data driven joins Brian Kraft 01/10/2011 03:35AM
Re: Data model metadata design and data driven joins Rick James 01/10/2011 09:51AM
Re: Data model metadata design and data driven joins Brian Kraft 01/10/2011 10:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.