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

joe_celko_at_my-deja.com wrote:

> 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.

Interesting, you are the exact opposite of my naming conventions!

I append 'Table' to may tables. I do this because I need to differentiate between the table and any records in that table. For example the 'CustomerTable' is different from 'CustomerClass' and different from 'customer'. The first is an object that holds records. The second is the relational schema for the table: it is possible to have more than one table with the same relational schema. Last is referring to a record in from the CustomerTable.

I never use plural form. Mixing plural with singular is confusing. When I refer to the CustomerTable as an object (eg. ALTER TABLE CustomerTable ADD COLUMN discount) it makes sense to use singular. I can also refer to it as a set when I make a query. Purality depends on the perspective. Is any one of my records a single object with attributes, or is it a set of field-value pairs? The answer depends on how I use it.

> >> As far as UserAccountTable goes, there are two options <<
>
> Go with #1, with the addition of Declarative Referential Integrity
> constraints

I would disagree. A separate primary key, as mentioned already, is easier to refer to by a FK. The separate primary key allows for more future extendibility. Maybe a table is going to keep track of the transactions:

3. TransactionTable:
TransactionTableID (PK)
UserAccountID (FK)
Amount

Would extend your design and keep to normal form. But without, you have

3. TransactionTable
User_ID (FK)
Account_ID (FK)
Amount

and would not be in normal form. Simply you can see that you can put invalid values into this table (transactions by a user that is not related to the account). Formally, there is a functional dependency between User_ID and Account_ID. Given a User_ID, the Account_ID has a restricted range.

The extra key appears to be a small price to pay for this extendibility.

> triggers or other DRI.

Constraints should be avoided as much as possible. There use appears to simplify the database schema, but actually just separates the true relationships into table design and constraint lists. It makes for a DB that is harder to manage.

> Why is this better?
>
> a) Less is more -- more normalized. You would have built redundant
> superkeys.

The TransactionTable I am backing is just as normal as the other. Although mine has future benefits.

> b) A table that is all key columns is always in Fifth Normal Form

True.

> c) Small is faster.

True again. Future extendibility is just one aspect that has to be considered speed and design ease are others that have to be balanced. I would say if you are NOT going to inherit this DB then loose the separate primary key, it will not be your problem. ;-)

Kyle Lahnakoski                                  Arcavia Software Ltd.
(416) 892-7784                                        www.arcavia.com