Re: [RDBO] triggers and overloading

[Guillermo Roditi]

SQL definitions are at the bottom if i am not clear enough....

yeah, i am basically trying to do a view, but myISAM doesnt support it....

basically this is what i have

I have users, user properties, and user property types and user property values.

A user is straight forward;
A user prop is a named property of a user that is in the form of a prop_type;
A user prop type is a data type definition for the purpouses of checking against a regex and detailing what data is;
A user prop value is the actual value stored.

Example:
User Joe has 2 personal and 2 business accounts.

Users
id   Name
1     Joe

User Props
id    type_id     name       
1       1        Business Account
2       1        Personal Account
3       2        Icon

User Prop Types
id   name        table     regex    usehash
1   Account #     char    /\d{6}/      0
2   Image         bin      NULL        1

User Prop Types Char
id     user_id     prop_id     p_value
1          1              1           123456
2          1              1           234561
3          1              2           345612
4          1              2           456123

User Prop Types Bin
id  user_id  prop_id  p_value   vhash
1      1        3      BLOB    FC4345F



I baiscally want the different types of property vlues to be as transparent to the interface as possible so i can load() a ::User  with=> 'prop_values'    and it automatically gets both binary type vvalues and character type values.

I have a shell class that's doing it right now. depending on the prop_id you specify it gives you the right object type, and when you say get() it push()es all the results for a query into an array and returns a ref to it.

I know there is going to be limitations because of the design, i just want it to be able to integrate as much as possible with the existing methods. I mean, I am not super dead set on it, but it would be nice if i could customize a special class in order to achieve the desired effect.


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

CREATE TABLE `user_prop_type_bin` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `prop_id` int(10) unsigned NOT NULL,
  `p_value` blob,
  `vhash` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_id` (`user_id`,`prop_id`,`vhash`)
);

CREATE TABLE `user_prop_type_char` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `user_id` int(10) unsigned NOT NULL,
  `prop_id` int(10) unsigned NOT NULL,
  `p_value` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_id` (`user_id`,`prop_id`,`p_value`)
);

CREATE TABLE `user_prop_types` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `regex` varchar(255) default NULL,
  `table` varchar(255) NOT NULL,
  `usehash` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
);

CREATE TABLE `user_props` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(40) NOT NULL,
  `nicename` varchar(80) NOT NULL,
  `description` text,
  `type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
);