Database Table Design

  • (2 Pages)
  • +
  • 1
  • 2

21 Replies - 1725 Views - Last Post: 25 July 2012 - 01:39 PM Rate Topic: -----

#16 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 24 July 2012 - 05:17 AM

I'm a little confused by what you mean by "You probably want to index up the BookCategories bridging table"

Can a table have more than one primary key? If so, then I'll make bookID and categoryID a primary key.

As far as the unique constraints, I'll have to refer to my old books to refresh my memory on those before I do anything on them.
Was This Post Helpful? 0
  • +
  • -

#17 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Database Table Design

Posted 24 July 2012 - 01:48 PM

A table can only have one Primary Key, but that Primary Key can be comprised of multiple columns. When it is comprised of multiple columns, it is called a Composite Primary Key. Bridging (or Junction) Tables can only have Composite Primary Keys, not ordinary Primary Keys, since the table describes a many-to-many relationship, and therefore no single column can act as a Primary Key.
Was This Post Helpful? 0
  • +
  • -

#18 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: Database Table Design

Posted 25 July 2012 - 04:18 AM

View Poste_i_pi, on 24 July 2012 - 08:48 PM, said:

Bridging (or Junction) Tables can only have Composite Primary Keys, not ordinary Primary Keys, since the table describes a many-to-many relationship, and therefore no single column can act as a Primary Key.

Technically that's not correct though. All that's required of a bridge table is that it has a unique composite key on the two foreign keys, but there is nothing that says it has to be the primary key. When the rows of the bridge table need to be referenced elsewhere, it's simpler to have a single integer act as the primary key instead of the composite key.
Was This Post Helpful? 0
  • +
  • -

#19 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 25 July 2012 - 05:27 AM

You both are making me realize just how much I don't remember from when I originally learned databases.
Was This Post Helpful? 0
  • +
  • -

#20 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 25 July 2012 - 11:58 AM

Is this what you are referring to with the composite primary keys?

create unique index bookcategories_unique on bookcategories (bookID, categoryID);

Primary key (bookID, categoryID);



Unfortunately neither of the books I own have much information on composite primary keys and how to use them. :/
Was This Post Helpful? 1
  • +
  • -

#21 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6064
  • View blog
  • Posts: 23,519
  • Joined: 23-August 08

Re: Database Table Design

Posted 25 July 2012 - 01:36 PM

Yes, that is a composite primary key.
Was This Post Helpful? 0
  • +
  • -

#22 RandomlyKnighted  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 117
  • View blog
  • Posts: 1,365
  • Joined: 14-January 10

Re: Database Table Design

Posted 25 July 2012 - 01:39 PM

Awesome, thanks JackOfAllTrades!
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2