Foreign Key Relationships and "belongs to many"

edit - Based on the responses below,开发者_JAVA百科 I'm going to revisit my design. I think I can avoid this mess by being a little bit more clever with how I set out my business objects and rules. Thanks everyone for your help!

--

I have the following model:

S belongs to T

T has many S

A,B,C,D,E (etc) have 1 T each, so the T should belong to each of A,B,C,D,E (etc)

At first I set up my foreign keys so that in A, fk_a_t would be the foreign key on A.t to T(id), in B it'd be fk_b_t, etc. Everything looks fine in my UML (using MySQLWorkBench), but generating the yii models results in it thinking that T has many A,B,C,D (etc) which to me is the reverse.

It sounds to me like either I need to have A_T, B_T, C_T (etc) tables, but this would be a pain as there are a lot of tables that have this relationship. I've also googled that the better way to do this would be some sort of behavior, such that A,B,C,D (etc) can behave as a T, but I'm not clear on exactly how to do this (I will continue to google more on this)

EDIT - to clarify, a T can only belong to one of A, or B, or C, (etc) and not two A's, nor an A and a B (that is, it is not a many to many). My question is in regards to how to describe this relationship in the Yii Framework models - eg, (A,B,C,D,...) HAS_ONE T , and T belongs to (A,B,C,D,...). From a business use case, this all makes sense, but I'm not sure if I have it correctly set up in the database, or if I do, that I need to use a "behavior" in Yii to make it understand the relationship. @rwmnau I understand what you mean, I hope my clarification helps.

UML:

Here's the DDL (auto generated). Just pretend that there is more than 3 tables referencing T.

-- -----------------------------------------------------
-- Table `mydb`.`T`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`T` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`S`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`S` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `thing` VARCHAR(45) NULL ,
  `t` INT NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_S_T` (`id` ASC) ,
  CONSTRAINT `fk_S_T`
    FOREIGN KEY (`id` )
    REFERENCES `mydb`.`T` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`A`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`A` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `T` INT NOT NULL ,
  `stuff` VARCHAR(45) NULL ,
  `bar` VARCHAR(45) NULL ,
  `foo` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_A_T` (`T` ASC) ,
  CONSTRAINT `fk_A_T`
    FOREIGN KEY (`T` )
    REFERENCES `mydb`.`T` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`B`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`B` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `T` INT NOT NULL ,
  `stuff2` VARCHAR(45) NULL ,
  `foobar` VARCHAR(45) NULL ,
  `other` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_A_T` (`T` ASC) ,
  CONSTRAINT `fk_A_T`
    FOREIGN KEY (`T` )
    REFERENCES `mydb`.`T` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`C`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`C` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `T` INT NOT NULL ,
  `stuff3` VARCHAR(45) NULL ,
  `foobar2` VARCHAR(45) NULL ,
  `other4` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_A_T` (`T` ASC) ,
  CONSTRAINT `fk_A_T`
    FOREIGN KEY (`T` )
    REFERENCES `mydb`.`T` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

---

Your problem is in part that you have no way to distinguish which of the tables it is in relation to.

Further if you can only have one record that matches any of three or four other tables, this is not a normal relationship and cannot be modelled using normal techniques. A trigger can ensure this is true but with only the column of id in it what prevents it from matching an id in table A of 10 and anid in table C of 10 (violating the rules).

BTW naming columns ID is usually a poor choice for maintenance. It is much clearer what is going on if you name the column with table name for PKs and use the exact name of the Pk for FKs.

An alternative solution for you is to have in the middle table a column for each type of id and a trigger to ensure that only one of them has values, but this is a pain to query if you need all the ids. A compound PK of id and idtype could work to ensure no repeats within a type, but to have no repeats at all, you will need a trigger.

---

This is a dilemma that comes up fairly regularly, and there is no perfect solution IMHO.

However I would recommend the following:

Combine the S and T table. I don't see any real need for the T table.

Invert the way the A/B/C tables relate to the S (formerly T) table. By this I mean remove the FK on the A/B/C side and create nullable FK columns on the S side. So now your S table has three additional nullable columns: A_ID, B_ID, C_ID.

Create a check constraint on the S table, ensuring that exactly one of these columns always has a value (or none of them has a value if that is allowed).

If having exactly one value is the rule, you can also create a unique constraint across these three columns to ensure that only one S can be related to an A/B/C.

If no value in any of these columns is allowed, the above rule will have to be enforced with a check constraint as well.

Update After Your Comment

Ok, then I would forget about inverting the relationships, and keep the FK on the A/B/C side. I would still enforce the uniqueness of usage using a check constraint, but it would need to cross tables and will likely look different for each flavor of SQL (e.g. SQL Server requires a UDF to go across tables in a check constraint). I still think you can nuke the T table.

Regarding the ORM side of things, I don't know yii at all, so can't speak to that. But if you enforce the relationship at the database level, how you implement it via code shouldn't matter, as the database is responsible for the integrity of the data (they will just look like vanilla relationships to the ORM). However, it may present a problem with trapping the specific error that comes up if at runtime the check constraint's rule is violated.

I should also mention that if there is a large (or even reasonably large) amount of data going into the tables in question, the approach I am recommending might not be the best, as your check constraint will have to check all 20 tables to enforce the rule.

---

You only require a table in the middle if it's a many-to-many relationship, and it doesn't sound like that's the case, so don't worry about those.

Your question isn't clear - can a T belong to more than 1 A, more than 1 B, and so on? Or does a single T belong to each of A-E, and to no others? It's the difference between a 1-to-1 relationship (each T has exactly one each of A-E) and a 1-to-many relationship (each A-E has exactly 1 T, but a T can belong to many A, many B, and so on). Does this make sense?

Also, I'd second the request for some more info in your question to help solidify what you're asking for.

---

I have to face a similar situation some weeks ago (not my own db, I prefer to combine all the tables into one, unless in very specific situations).
The solution I implemented was: In "T" model file I did something like this at relations() function:

'id_1' => array(self::BELONGS_TO, 'A', 'id'),
'id_2' => array(self::BELONGS_TO, 'B', 'id'),
'id_3' => array(self::BELONGS_TO, 'C', 'id'),

I hope this helps you.
Regards.