Manage Model Relations On Three Tables

Yii 1.1 General Discussion
1

I have 3 tabels with this kind of structure:

[font="Courier New"]user:

id pk

username

password

type type_a|type_b

other data…

type_a:

user_id

data for type_a

type_b:

user_id

data for type_b[/font]

when I will generate Model how can I write this kind of relation in proper way?




class User extends CActiveRecord

{

    ......

 

    public function relations()

    {

        return array(

            'type_a'=>array(self::HAS_ONE, 'Type_a', 'user_id'),

            'type_b'=>array(self::HAS_ONE, 'Type_b', 'user_id'),

        );

    }


    public function otherData(){

    if($this->type=='type_a') return $this->type_a;

    if($this->type=='type_b') return $this->type_b;

    return null;

    }

}

Can be right or there are more clever/elegant solutions?

2

This doesn’t appear to be very good database design. Rather than trying to make this example work I’d look at restructuring and that should make things easier.

If you let me know the reason you’re trying to separate out two types into two tables maybe I can help a bit more.

3

Every User has inside an address and some common stuff, the type_a is a single person, the type_b is a venue. The single person will have different attributes than venue. If I create two tables I will repeat same common informations and if I search I need to to two queries and merge them on common data.

4

You could make a meta table like WordPress uses. This would be more flexible with adding new fields down the road and also get rid of a table. You can still store the type in the users table, because ever user will have a type so that’s common information. Then in your code you can handle what meta data gets added based on the users type.

users:

user_id (pk)

username

password

address

type

usermeta:

meta_id (pk)

user_id (fk)

meta_key (ex. company)

meta_value (ex. Microsoft)

5

thanks :D