Wednesday 10 November 2010

importing external data to mySQL

For a project i was working on I had to import a csv file provided by booking.com
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);


2 comments:

  1. JOHNN'S CASINO IN SEAH OLYMPUS CITY - Air Jordan
    JOHNN'S CASINO IN SEAH OLYMPUS CITY top air jordan 18 retro men blue JOHNN'S CASINO IN SEAH OLYMPUS CITY JOHNN'S CASINO IN SEAH OLYMPUS CITY JOHNN'S CASINO air jordan 18 stockx clearance IN SEAH air jordan 18 retro toro mens sneakers free shipping OLYMPUS 188 벳 CITY air jordan 18 retro red from me | JOHNN'S

    ReplyDelete
  2. This multi-highlighted module completes various programmed assignments to assist with advancing your situation, including inward connections and recommending the most proper watchwords for you to focus on each page.
    http://wordpressctapro.com/

    ReplyDelete