SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)

    Join Table Index

    So here's another design question;

    Should there be a defined index for a join table, if that index would have to be the entirity of the join table?

    IE:
    I have two axes of a matrix as the keys of the join table. This forms a many-to-many relationship between the two.
    table1
    -----
    fk1 INT(5),
    fk2 INT(5),
    data VARCHAR(50)

    fk1,fk2 and data are not unique. (fk1,fk2) is not unique. Neither is (fk1,data) or (fk2,data). So the only index possible is (fk1,fk2,data), which is unique, but seems rather moot when the order of the data in the result query is not a factor and would result in the index being the entire table.

    Am I missing anything here, oh great gurus of the databases?

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should add an auto_increment PK in this case. It'll make your individual records indexable.

    table
    -------
    id int unsigned not null auto_increment primary_key,
    fk1 int unsigned not null references table1(id),
    fk2 int unsigned not null references table2(id),
    data varchar(50) null


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •