2

I have a many to many table (TableAB) which simply holds a foreign key to table A and a foreign key to table B. It always feels like I should also add a primary key column to the table itself, and that's what I'd normally do. But from an academic point of view, does that violate any of 2nf, 3nf, BCNF rules? It doesn't add any real uniqueness, and creates a table where I have a primary key and a separate composite primary key although neither overlapping, but does make relations easier to work out when my M2M table is then referenced by other tables, but interested to hear from the academics out there on their opinion.

ie.

TableA ( Aid int PK )

TableB ( Bid int PK )

TableAB ( Aid int, Bid int, ABID int PK ?????? )

thanks

Ricky
  • 21
  • 1
  • 7
    In that case I would make my primary key a composite of `Aid` and `Bid`. – Jeremy Holovacs Jan 28 '13 at 20:44
  • I cannot recall the last time I had a Surrogate PK on an M-M. The Natural PK (A,B) is just too easy in this case. –  Jan 28 '13 at 20:47
  • That's what I'm starting to think. The only problem though if i were to have TableABCDEF where the composite primary key were to be made up of, say, 6 foreign keys, if I were then to create another table to link to TableABCDDEF, I would have to include all of those keys on that table too. Not saying that's wrong, just feels like things could get out of hand quite quickly in a large database. It doesn't feel like it violates any of the normalisation rules but I would like to know what the academically 'correct' way of doing it is. – Ricky Jan 28 '13 at 20:48
  • Generally you will not make a many-to-many link table with more than 2 foreign keys unless you are doing OLAP stuff. – Jeremy Holovacs Jan 28 '13 at 20:49
  • I would venture to state that the Academically "Correct" way never - although I'm sure someone has argued for some exception - involves a Surrogate Key. (However, in Practice, there are some .. practical reasons to do so, including separation of the "same data".) –  Jan 28 '13 at 20:49
  • Clearly your primary key is Aid,Bid. Using a surrogate key is often discouraged, because in reality, the surrogate is just a placeholder for the compound key. From academic stand point, the surrogate key is bad, in reality you have to consider the gains of using such a key. – Ikstar Jan 28 '13 at 20:53
  • Normalization never introduces a new column. – Mike Sherrill 'Cat Recall' Aug 30 '13 at 00:31

1 Answers1

1

From an RDBMS theory point of view you will have a composite primary key on TableAB.

Example

CREATE TABLE IF NOT EXISTS `TableAB` (
  `A_id` int(11) NOT NULL,
  `B_id` int(11) NOT NULL,
  PRIMARY KEY (`A_id`,`B_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The need for a separate primary key comes from needs of software that works with the RDBMS. Some of the Microsoft tools do not allow updating the primary key, so one has to delete and add the row to modify it. Having a separate primary key makes it easier to update the relationships.

In the world of coding by convention, frameworks like CakePhp or Rails kind-of expects a column named ID to be the primary key of a table. http://guides.rubyonrails.org/association_basics.html#the-has_and_belongs_to_many-association

Yii php framework allows composite primary key so we can define tables as

$this->createTable('course_student', array(
    'course_id' => 'INT NOT NULL',
    'student_id' => 'INT NOT NULL',
    'PRIMARY KEY (`course_id`,`student_id`)',
        ), 'ENGINE=InnoDB');
$this->addForeignKey("fk_cs_course", "course_student", "course_id", "course", "id", "CASCADE", "RESTRICT");
$this->addForeignKey("fk_cs_student", "course_student", "student_id", "student", "id", "CASCADE", "RESTRICT");
Xavier John
  • 8,474
  • 3
  • 37
  • 51