International addresses - need some help



  • I've hit a bit of a brick wall. I'm developing a PHP script to walk a user through entering a vacation home listing; I've got it 99% working the way I want it to. Where I've hit the wall is getting the data actually written to the database.

    MySQL version is 4.0.23 and PHP version is 4.3.10. 

    I may have overengineered things a bit; addresses are broken into 2 (well, technically three) tables; one for general information that is common to all addressses (geographical area, country, city) and then a table for the US-centric address details (state, zip) and one to handle international addresses.

    The trick is how to link the two together.

    Here are the table definitions:

    address (
      address_id INT(10) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
      owner SMALLINT(5) UNSIGNED DEFAULT '0', // references site user
      type TINYINT(1) UNSIGNED DEFAULT '0' NOT NULL,
      continent TINYINT(2) UNSIGNED DEFAULT '0', // continent, country, and region cross-reference other tables.
      country SMALLINT(5) DEFAULT '0' NOT NULL,
      region SMALLINT(5) DEFAULT '0' NOT NULL,
      city VARCHAR(50) DEFAULT '' NOT NULL,
      us_address INT(10) DEFAULT '0' NOT NULL, // supposed to reference us-specific address components
      intl_address INT(10) DEFAULT '0' NOT NULL, // for everyone else; a given address will use one or the other.
      PRIMARY KEY (address_id)
     )

     us_address (
      us_address_id INT(10) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT,
      owner SMALLINT(5) UNSIGNED DEFAULT '0',
      box INT(10) UNSIGNED DEFAULT '0' NOT NULL,
      street VARCHAR(255) DEFAULT '' NOT NULL,
      zip VARCHAR(20) DEFAULT '' NOT NULL,
      PRIMARY KEY (us_address_id)
    );
     

    So, my question: how do I create entries in these tables simultaneously and reference one from the other? The SQL reference doesn't seem to give me away of finding out "hey, that INSERT statement created record FOO" so I can use it for the other table. And since users can have multiple listings, I need to make sure the tables are consistent.

    So, am I overengineering it and should I just dump all the data into the main table, or is there a way to do what I want that I'm missing?

     Thanks,

    Nathan

     



  • I don't have  a lot of experience with DB design, so take this with a grain of salt (or correct me as needed.) I see two questions here:

     1) How do you get the value of an auto_increment field after inserting? This depends on the database software that you're using. Usually the global and per-connection/transaction last-ID value is available through some variable or function. In MySQL it's LAST_INSERT_ID(), or http://www.webtricks.com/sourcecode/code.cfm?CodeID=23 for some SQL/Oracle info

     

    2) Since this is ALWAYS a 1-to-1 relationship, it would generally be pulled into one table. There's no real requirement to split it up. I would pull all the fields into one table (we really aren't freaks on this side of the ocean, we use pretty much all the same address components.) Use the country field to validate/format the address apropriately.
     



  • Thanks for your input.

    I'm really not looking to create address templates for every single country; basically I'm creating US-style address forms for US & Canada, and letting everyone else just enter their addresses on up to 5 blank lines (with the 6th automatically being filled with the country name).

    But you're right--it's a 1-to-1 relationship, it's unnecessary; thinking too much like a programmer. :) =

    Nathan

     



  • Unless you're going to have a TON of records where having some extra NULL columns in each record will devastate disk space, I'd say just lump it all into one table to save yourself all the join headaches. Allow NULLs in the columns that are optional/country-specific, and you should be fine. I think MySQL supports triggers, if you want to do some validation to make sure that at least enough info is provided.



  • use some stored procedures wizardry... 

    addressinsert (......parameters following my-sql syntax and table columns, output parameter for that ID field)

    us_addressinsert(....parameters following my-sql syntax and table columns, using the parameter returned from addressinsert)



  • [quote user="Ghost Ware Wizard"]

    use some stored procedures wizardry... 

    addressinsert (......parameters following my-sql syntax and table columns, output parameter for that ID field)

    us_addressinsert(....parameters following my-sql syntax and table columns, using the parameter returned from addressinsert)

    [/quote] I believe the key point is "MySQL 4"


Log in to reply