Re: DB Design question: Do you create a separate PK in the join table?

>> We are designing a new oracle database for shop. We just had a long,
heated debate about whether we should create an independent Primary key in join tables. For example:
We have three tables: UserTable, AccountTable, UserAccountTable. And we want to support that a user can have several account and an account can be owned by several users (join account). <<

First of all, quit namin tables <something>Table -- the whole idea of a relational datarelational database is that you do not care about PHYSICAL storage, only LOGICAL meaning in your data model. Next use plural names or collective nouns for the tables -- they are SETS and not single data elements.

>> As far as UserAccountTable goes, there are two options <<

Go with #1, with the addition of Declarative Referential Integrity constraints

 CREATE TABLE UserAccounts
 (user_id INTEGER NOT NULL REFERENCES Users(user_id)

          ON DELETE CASCADE
          ON UPDATE CASCADE,
  account_id INTEGER NOT NULL REFERENCES Accounts(account_id)
             ON DELETE CASCADE
             ON UPDATE CASCADE,

I am assuming some business rules about what happens when a user or his account is removed from the database, so you might change this code to triggers or other DRI. Why is this better?

1. Less is more -- more normalized. You would have built redundant superkeys.
2. A table that is all key columns is always in Fifth Normal Form
3. Small is faster.

--CELKO-- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 12 2000 - 00:00:00 CET