implementing a 1:1 realtion in migration script

Yii 2.0 General Discussions
1

Hi guys,

follwoing code will implement 1:n relation between two tables,correct?

If I want to implement 1:1 relation in MySQL, I have to set Foreignkey to unique. How can I set a constraint in yii to unique. Official documentation shows this




public void addForeignKey ( $name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null

My migration script looks like this:




.

.


        $this->createTable('bewerber', [

            'id' => $this->primaryKey(),

            'id_person' => $this->integer(10),

	.

	.

	[

            'FOREIGN KEY ([[id_person]]) REFERENCES person ([[id]]) ON DELETE CASCADE ON UPDATE CASCADE',

	.

	.

        ], $tableOptions);

.

.
2

foreign key does not have to be unique and also it does not have to be 1:n, you can create a 1:1 and still call addForeignkey with details accordingly.

UPDATE: here is an example


// add foreign key for table `user`

$this->addForeignKey(

    'fk-bewerber-id_person',

    'bewerber',

    'id_person',

    'person',

    'id',

    'CASCADE',

    'CASCADE'

);
3

Häh?

[size="3"]This is my attention to achieve:[/size]




CREATE TABLE bewerber(

id INT NOT NULL AUTO_INCREMENT,

id_person INT NOT NULL UNIQUE, // this will implement 1:1 Cardinality in MySQL

FOREIGN KEY (id_person) REFERENCES person(id)

    ON DELETE CASCADE

    ON UPDATE CASCADE,

PRIMARY KEY (id));

I also got your example in official documentation,but this example will implement 1:n relation,not 1:1

4

Adding "unique()" should be enough in the database layer:




        $this->createTable('bewerber', [

            'id' => $this->primaryKey(),

            'id_person' => $this->integer(10)->unique(),

	.

	.

	[

            'FOREIGN KEY ([[id_person]]) REFERENCES person ([[id]]) ON DELETE CASCADE ON UPDATE CASCADE',

	.

	.

        ], $tableOptions);

In the ActiveRecord layer, however, Gii may generate a "has-many" relation for it:




/* in Person.php */


/**

 ...

  * @property Bewerberg[] $bewerbergs

 ...

*/


public function getBewerbers()

{

    return $this->hasMany(Bewerber::className(), ['person_id' => 'id']);

}

You can modify it to "has-one" relation:




/* in Person.php */


/**

 ...

  * @property Bewerberg $bewerberg

 ...

*/


public function getBewerber()

{

    return $this->hasOne(Bewerber::className(), ['person_id' => 'id']);

}
5

From what I have learned about relational database design, you don’t use foreign keys or anything like this for relations that will always be 1:1. You just combine them in the same record by adding the necessary columns to the table.

For example




Table A

ID_A

Field A1

Field A2

...

Field An


Table B

ID_B

Foreignkey_A_ID

Field B1

Field B2

...

Field Bn

Would simply become




Table AB

ID_A

Field A1

Field A2

...

Field An

Field B1

Field B2

...

Field Bn

This is way more performant as the database engine doesn’t have to combine data every time you access it. Of course this only works for 1:1 relations. If the relation is optional, you can define the optional fields to allow a NULL value.

In your case, the "person" table could simply get a separate column "is_bewerber", which could be a boolean.

(Oh how I hate those Denglish identifiers. That’s why I code in English only.) :P

If you wanted to see applicants only , you would simply add a check for this column to be TRUE.

6

Yeah, I agree. :)

7

IMO it depends on the domain your trying to model, imagine a use case where you have different types of users maybe admin and user with following schema.


admin

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

username

password

is_active

created_at

update_at


user

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

username

password

is_active

full_name

address

phone

email

created_at

update_at

now if you use only one table to represent the models, you will have lot of empty columns for admin users adding more fields means admin will also have those columns, a better design would be to split this into two tables users, profiles which is good use case of 1:1.


admin

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

username

password

is_active

created_at

update_at


user

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

username

password

is_active

created_at

update_at




profile

---------

full_name

address

phone

email

user_id

with this design i can have all my user columns saved in one table and additional profile fields can be stored in profiles tables without any empty columns/fields for admin.

again its a personal choice storing it one table might give you ease of use, but the latter design is much better in terms of flexibility.

8

@alrazi

I just remembered I myself designed my User and Profile using 1:1 relation. :P

9

This is,what I was searching for. Thx for this!

I can’t generate seperate columns in database. My job is to generate script based on Entity-Relationship-Model. m:n already have been disbanded, PK,FK and properties are firmly anchored

So,it’s impossible for me to change table(s),attribute(s),cardinality etc…