Unified Relational Model into one

Yii 1.1 General Discussion
1

Hi there, my first post!

Well, the thing is I’m very new with yii and I was trying to get experience in it.

This is the scenario

[size="2"]Tables[/size]

user_entity

with a foreign key user_type

user_entity_type

with the user_type autoincrement and name columns.

Well, if I use Gii tool, this will create to models: UserEntity and UserEntityType.

But I want to have it into one.

I would need to create a custom model (made by me) like Users and integrate there both models?

Then, with the CRUDs of UserEntity and UserEntityType, the same thing:

  • Integrate those into one and point to the custom model Users: protected/views/users/*

???

And, if all of this is correct, do I need to define in my Users Model public functions like Gii tool does?

Ex. tableName(), search(), rules(), relations(), attributeLabels(), etc…

BTW, I already love Yii!

Thanks in advance,

2

Hi cactork,

so far I dont think I understand what your are trying to do.

just to get you right:

If you can mix up UserEntity and UserEntityType in one custom model (-> assuming a 1:1 relation), why do you want to keep them in separate tables?

Next question: You mention an autoincrement in UserEntityType which seems to be independent from entries in UserEntity (-> 1:n or n:n relation?).

What is the real relation between those tables? Please, could you give examples of both tables?

cheers

Steffen

3

Hi Steffen, thanks to take your time to response.

The thing is that I want to be able to create different types of user, that’s why I created a table of user_entity_type.

Let’s say today I have:

user_entity

user_id (autoincrement key)

type_id - foreign key in user_entity_type

data_id - foreign key in other table, handling user profile information.

user_entity_type

type_id (AI key)

name - string

So, if I generate this with Gii tool, I’ll have two models.

But I want to know if I can do it with one.

Thanks!

4

ok, as far as I understand you want to generate something like




user_id         |   type_id      |      user_type_name           |      data_id 

   1            |       1        |           friend              |        ...

To get these values in one grid in the view you can use relational ActiveRecords:

e.g. from the UserEntity model




$model->user_id | $model->type_ID | $model->userentitytype->name | $model->data_id

As a prerequisite you need to define a relation in your UserEntity model




public function relations()

{

	return array(

		'userentitytype'=>array(self::BELONGS_TO, 'UserEntityType', 'type_id'),

	);

}

That way you keep the data separate/normalized and at same time can have it in one view.

Hope this helps.

5

Looks like a single table inheritance.


CREATE TABLE `car` (

    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    `name` varchar(255) NOT NULL,

    `type` varchar(100) NOT NULL,

    PRIMARY KEY (`id`)

);





class Car extends CActiveRecord {

    static function model($className=__CLASS__) {

        return parent::model($className);

    }

 

    function tableName() {

        return 'car';

    }

 

    protected function instantiate($attributes){

        switch($attributes['type']){

            case 'sport':

                $class='SportCar';

            break;

            case 'family':

                $class='FamilyCar';

            break;

            default:

                $class=get_class($this);

        }

        $model=new $class(null);

        return $model;

    }

}







class SportCar extends Car {

    static function model($className=__CLASS__) {

        return parent::model($className);

    }

 

    function defaultScope(){

        return array(

            'condition'=>"type='sport'",

        );

    }

}

 

class FamilyCar extends Car {

    static function model($className=__CLASS__) {

        return parent::model($className);

    }

 

    function defaultScope(){

        return array(

            'condition'=>"type='family'",

        );

    }

}

Now using Car you’ll get both SportCar and FamilyCar.

6

Good example steffen, that work so nice!

But I think that inheritance classes it’s what I need.

So, I can create main model of Users that have the other two models. Nice tip samdark.

I’ll try and post the final result.

Thanks again!

7

samdark, your post comes right in time for me. I just was thinking about how to work with inheritance between AR classes and like your approach very much. Thanks for sharing!

Sorry, for taking over this thread a little, but it’s probably related:

Any suggestion how to work with different sets of attributes for the different sub types?

For example, take the following attributes:

Shared by all car types: name, power, max_speed, …

Family cars: number_of_seats, …

Sport cars: power_to_wind_ratio, …

I thought about hardcoding the additional attributes in the type classes and store their values in another table car_attributes like this:




id (INT) - id of attribute

car_id (INT) - id of car (parent)

name (VARCHAR) - name of attribute ('number_of_seats', 'power_to_wind_ratio')

datatype (TINYINT) - id of datatype (1=string, 2=int, 3=float, ...)

value_string (VARCHAR) - value for datatype string

value_int (INT) - value for datatype int

value_float (DECIMAL) - value for datatype decimal

Each dynamic attribute value would be represented by a row in this table. The column ‘datatype’ stores the type of data and there’s one data column for every possible datatype (value_string, value_int, value_float, etc). Using a HAS_MANY relation in Cars with the ‘index’ feature would allow easy access to the attribute records:




'dynamics'=>array(self::HAS_MANY, 'CarAttributes', 'car_id','index'=>'name'),







$familycar=FamilyCar::model()->with('dynamics')->findByPk($sportcar_id);

// safety checks like isset(...) left out for clarity...

$seatNumber=$familycar->dynamics['number_of_seats']->value_int;

But as you see this makes it more complicated to access the value of such a dynamic attribute. And the "one-column-per-type" approach is not very ressource friendly. Not to mention that there are more types than i gave in the example.

Ideas? Alternative approaches?

8

When I faced a similar problem, I created different tables for sub types. But in this case you’ll need to join a main table with one of others depending on main.car_type value.

Your approach reminds me of EAV Model, which I didn’t use and don’t like it very much.

Anyway, the question is still interesting, but I think there is no perfect solution in Relational DBMS.

9

Hi I think I may have a similar question and wish to get further insight on steffans input

I refer to my post

Are you saying steffan that I should keep the data within there separate models…modify the relations and create a "View"( or Controller?) that merges the 2 data information together to be displayed?

Seems fitting for a 1 to 1 relationship but what about 1 to many?