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

Hi !

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

We discussed the same question several months ago. The short results is following:

Ideal choise should depend on meaning the join table You are designing. You shold check if is there any necessity to reference from somewhere to Your join table. If yes, there should be an independent single-column primary key in table to avoid cascade updates (by the way, dont forget about unique constraint on others columns if applicable as in usual join tables). Also You'd check if any additional attributes of a join is required or possible. For example, for customer-account join there could be some related attributes like 'default account' boolean value or something similar. There is quite big probability You'll have to reference to it in this case. And more - You'd check if one of this conditions can become true somewhere in the future.

We checked most join tables of our database and none of them satisfied both conditions. Therefore, we desided that any join table shod have it's own single-column primary key. This is not too difficult for coding but we can be sure that we can reference to join recodrs id necessary. Anyway, reverse side conversion (from complex PK to independent) will be much more difficult.

--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)

Igor V. Podolsky (igoryok_at_soft-review.kiev.ua) Received on Thu Jan 13 2000 - 05:30:08 CST