The constraint of this file is that i wanted to be able to allow to reimport that file as booking.com publishes updates with a list of new hotels quite often. That means that if another table in the database relies on the data of the imported file we can't create a normal foreign key as when we reimport the file, we can't guarantee the primary key will remain identical.
I decided to create the foreign key to a hash of the combination of town name and country name, why not only the town name? as for example London exists in England and in Canada, which would cause a problem as we want the foreign key to have only one option in the imported table.
Symfony doesn't know how to handle none numerical values of foreign key, hence we need to do the task of the association between the tables ourself.
in my schema.yml i configured the table PromoteTown that has a relation to BookingComHotels
PromoteTown:
columns:
town_hotel_id:
type: BINARY(16)
notnull: true
unique: true
position:
type: integer(4)
relations:
BookingComHotels: { local: town_hotel_id, foreign: id_hash, onDelete: cascade, foreignAlias: PromoteTowns }
and in the model i have a method to insert to PromoteTown, the $this is related to BookingComHotels as this function is in BookingComHotels class.
$hash = $this->getIdHash();
$pt = new PromoteTown();
$pt->type = '2';
$pt->town_hotel_id = $hash;
$pt->save();
$pt->link('BookingComHotels', $hash);
