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

Kyle Lahnakoski <kyle_at_arcavia.com> wrote in message news:387DFFB6.B15A336D_at_arcavia.com...
>
> 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

This is in normal form! Just make the right relationship (read on...)

I agree with Joe on naming conventions and on the PK-stuff as well.

I think you should build the transactions table as follows:

3. Transaction
User_ID (PK)(FK1)
Account_ID (PK)(FK1)
Datetime (PK)
Amount

FK1 is ONE combined relationship pointing to the UserAccount table. In this way, it is not possible to insert transactions by a user that is not related to the account. An alternative for the transactions table:

3. Transaction
Transaction_ID (PK)
User_ID (FK1)
Account_ID (FK1)
Amount Received on Mon Jan 17 2000 - 00:00:00 CET