0

Just figured out that it appears the only way to map many-to-many relationship is via a junction table. Looking for advice on the pros and cons of the two approaches I've come up with.

SAMPLE_DATABASE:

table_01
-- table_01_id (pk)
-- table_01_data

table_02
-- table_02_id (pk)
-- table_02_data

Junction table (1)

junction_id (pk)
table_01_id (fk)
table_02_id (fk)

Junction table (2)

table_01_id (pk)
table_02_id (pk)

Guessing Junction table (2) is the way to go, but interested in knowing why.

Thanks!

blunders
  • 3,619
  • 10
  • 43
  • 65
  • 3
    possible duplicate of [Do link tables need a meaningless primary key field?](http://stackoverflow.com/questions/1843222/do-link-tables-need-a-meaningless-primary-key-field) – OMG Ponies Dec 07 '10 at 00:24
  • 1
    Also: http://stackoverflow.com/questions/3647797/what-would-it-mean-if-i-change-the-identifying-relationship-from-this-part-of-a-d – OMG Ponies Dec 07 '10 at 00:27
  • @OMG Ponies: Thanks, so it better to delete the question, or let it be closed? – blunders Dec 07 '10 at 00:29

2 Answers2

2

See Do link tables need a meaningless primary key field?

Community
  • 1
  • 1
btiernay
  • 7,873
  • 5
  • 42
  • 48
1

The junction_id (pk) in Junction table (1) is meaningless. It's a waste to have it, so don't.

jgritty
  • 11,660
  • 3
  • 38
  • 60
  • 1
    While meaningless to the schema, it enforces an arbitrary physical write order for the table. Composite keys have some extra complexity involved in inserts and B+-tree balancing. On the whole, though, you should use two columns as a non-clustered PK and only add a clustered identity/sequence column if you start running into I/O problems caused by index maintenance issues. –  Dec 07 '10 at 00:29
  • @Jeremiah Peschka: Great, thanks -- that was the info I was looking for, and guessed was the answer too. – blunders Dec 07 '10 at 00:31
  • @Jeremiah- But chances are you would want a composite index and unique constraint on `table_01_id, table_02_id` anyway which would still have to be maintained. – Martin Smith Dec 07 '10 at 01:09
  • 1
    @Martin - that's correct, but maintaining B+-trees in index supported tables is, IIRC, much faster than maintaining B+-trees on heap tables. MySQL attempts to skirt this problem by adding an arbitrary clustering column if you don't provide one AND you mark your PK as non-clustered. Either way, marking your PK (a unique, composite index in this case) as non-clustered in MySQL is a perfectly safe thing to do... until they change the behavior without telling us. –  Dec 07 '10 at 12:34