ansaurus

Question

Answer 1

A:

your tables a properly joined. i think the problem you have here is that you have normalized your data improperly and gone too far. just store the address as provided. don't try and split it in to table with numeric ID's.

for example, what possible benefit do you get by storing the state as a number in the contact table instead of just the state code? and the same question applies to city.

longneck 2009-10-30 17:52:45

State, city, and zip code data can be used in a large variety of places. Table nomalization will not help here. Rethinking the surrogate keys would however.

Kevin Peno 2009-10-30 17:58:48

Answer 2

+1 A:

I believe that you should re-think the surrogate key usage on a lot of these tables and use natural keys where possible. Taking the state table for an example, in most cases it will be acceptable to simply use the state short (ie. TX vs. Texas) for data and display purposes. This means that if you removed the incrementing ID on the states table and used a natural key for each state, you would reduce the necessecity of a join in 90% of cases.

Then use state.abbriviation as the FK in tables that need to store state values. Extending this to zipcodes and cities, you can FK the state abbr to the city table, and make a compound FK form the cities table to the contacts table giving you a key for both the city and the state at the same time.

Example schema (excluded zipcodes table and shortened contacts table):

CREATE  TABLE IF NOT EXISTS `states` (
  `state_id` CHAR(2) NOT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`state_id`) ,
  UNIQUE INDEX `state_name` (`name` ASC)
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `cities` (
  `state_id` CHAR(2) NOT NULL ,
  `city_name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`state_id`, `city_name`) ,
  INDEX `fk_city_state_id` (`state_id` ASC) ,
  CONSTRAINT `fk_city_state_id`
    FOREIGN KEY (`state_id` )
    REFERENCES `states` (`state_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `contacts` (
  `contacts_id` INT NOT NULL AUTO_INCREMENT ,
  `state` CHAR(2) NULL ,
  `city` VARCHAR(255) NULL ,
  PRIMARY KEY (`contacts_id`) ,
  INDEX `fk_contact_city` (`state` ASC, `city` ASC) ,
  INDEX `fk_contact_state` (`state` ASC) ,
  CONSTRAINT `fk_contact_city`
    FOREIGN KEY (`state` , `city` )
    REFERENCES `cities` (`state_id` , `city_name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_contact_state`
    FOREIGN KEY (`state` )
    REFERENCES `states` (`state_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Data for table `states`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `states` (`state_id`, `name`) VALUES ('TX', 'Texas');
INSERT INTO `states` (`state_id`, `name`) VALUES ('CA', 'California');
INSERT INTO `states` (`state_id`, `name`) VALUES ('OR', 'Oregon');
COMMIT;

-- -----------------------------------------------------
-- Data for table `cities`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('CA', 'modesto');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('OR', 'protland');
INSERT INTO `cities` (`state_id`, `city_name`) VALUES ('TX', 'Dallas');
COMMIT;

Now your query is simplified except in the extreme case where you'd need a full state nomenclature:

SELECT
    concat(contacts.last,' ', contacts.first) as name,
    city,
    state,
    zip
FROM contacts
WHERE {INSERTWHERE}

Kevin Peno 2009-10-30 18:14:42

Interesting - I had not realized foreign keys were supported in mysql - I always thought they didn't. Im going to try this solution out and see how it works.ThanksRich

Richard Testani 2009-10-30 23:58:16

They are supported, provided you are using INNODB :)

Kevin Peno 2009-10-31 00:27:31

Im working out this example, but with a few questions. Would you create a similar relationship with zips on city_name -> zipcode?Also, in the contacts table - to look up the full state name, I would continue to use joins. Correct?Thanks for you help on this.

Richard Testani 2009-10-31 15:41:08

You would indeed need to do a join (or separate query) to get the FULL name of a state. For City -> ZipCode relationship, I'd probably put a FK in the city table for the zipcode (to associate it with a city), but not necessarily make it part of the PK and then put an FK in the contacts table pointing to the zipcodes table (this would make sure that you are entering valid zipcodes). If you need to validate City, state, and zip, perhaps just bring an FK from the cities table with all 3 fields. It really depends on your setup.

Kevin Peno 2009-11-02 18:17:10

ansaurus

tags:

views:

answers:

Help with MySQL Query with many Joins

related questions