I'm having some trouble figuring out how to make these foreign key relationships work with fORM. Here is what I want to accomplish. I've got a users table with some basic columns, an item1price column, and an item2price column. I have tables called item1prices and item2prices. I want each user to be assigned to one of the price levels I've defined in the item1price, item2price, etc... tables. (These prices levels will be shared amongst the users, so I can't use a one-to-one relationship since the price ID in the users table won't be unique). How do I access the pricing for say item1 on a user? I can't seem to figure it out. Everything I've tried has resulted in an error telling me the relationship isn't one-to-one.
CREATE TABLE IF NOT EXISTS `users` (
`user_id` VARCHAR(50) NOT NULL ,
`email` VARCHAR(255) NOT NULL ,
`password` VARCHAR(255) NOT NULL ,
`item1price` INT NULL ,
`item2price` INT NULL ,
PRIMARY KEY (`user_id`) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
CONSTRAINT `user_item1price`
FOREIGN KEY (`item1price` )
REFERENCES `item1prices` (`price_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `user_item2price`
FOREIGN KEY (`item2price` )
REFERENCES `item1prices` (`price_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB