User Tools

Site Tools


db:relations

Relation types exemplified in Doctrine YAML Files

12.01.2009

1. ONE TO ONE

Case: two tables, sn_user and sn_cnp. One user has one cnp (social security code). Deleting an user will remove also the related cnp record.

SnUser:
    columns:
        name: { type: string(100) }
        age: { type: integer(1) }
    options:
        collate: utf8_unicode_ci
        charset: utf8
    relations:
        SnCnp: { local: id, foreign: user_id }

SnCnp:
    columns:
        cnp: string(13)
        user_id: integer
    relations:
        SnUser:
            local: user_id
            foreign: id
            onDelete: CASCADE
            foreignType: one

2. ONE TO MANY

Case: sn_user and sn_phone_numbers. One user may have multiple phonenumbers.
Deleting an user will remove also all related phonenumbers records.

SnUser:
    columns:
        name: { type: string(100) }
        age: { type: integer(1) }
    options:
        collate: utf8_unicode_ci
        charset: utf8
    relations:
	...
        RelPhonenumbers:
            type: many
            class: SnPhoneNumbers
            local: id
            foreign: user_id

SnPhoneNumbers:
    columns:
        user_id: integer
        phone_number: string(20)
    relations:
        SnUser:
            local: user_id
            foreign: id
            onDelete: CASCADE

3. MANY TO ONE

Case: sn_user and sn_level tables. One user can have only one level, but one level can be related with many users.
A level record cannot be deleted if it's related to at least one user record.

SnUser:
    columns:
	...        
	level_id: integer
    relations:
	...
        RelLevel:
            class: SnLevel
            local: level_id
            foreign: id
            onDelete: RESTRICT

SnLevel:
    columns:
        level: integer
    relations:
        SnUser:
            local: id
            foreign: level_id

4. MANY TO MANY

Case: sn_user and sn_city tables (for relation a supplementary table will be created sn_user_city). One user can have one or more cities, one city can be related to one or more users (or none).
A city cannot be deleted if it's already linked to an user, deleting an user will delete also the relation with the city.

SnUser:
   relations:
        RelCityU:
            class:              SnCity
            foreignAlias:       RelUserC
            refClass:           SnUserCity
            local:              user_id
            foreign:            city_id

SnCity:
    columns:
        name: string(10)
    relations:
        RelUserC:
            class:              SnUser
            foreignAlias:       RelCityU
            refClass:           SnUserCity
            local:              city_id
            foreign:            user_id

SnUserCity:
    columns:
        user_id: integer
        city_id: integer
    relations:
        SnUser: { onDelete: CASCADE, local: user_id, foreign: id }
        SnCity: { onDelete: RESTRICT, local: city_id, foreign: id }

db/relations.txt · Last modified: 2013/03/16 17:40 (external edit)