| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dilemma in choosing the candidate for the primary key.
> In a new design I would create a new number column - sequence (maintain
> it in the trigger) to define the primary key. My feeling is that it
> would require less storage space in the index and as a foreign keys.
Quite right, but ...
> Does anybody has such as dilemma to choose PK canditate from the natural
> column or to add one artificial? Do you have some experience in chossing
> one or another approach? Any pros and contra in the sence of
> performance, storage, way of using...?
I can cite people who had the problem : those Larry E., Bob M., Derry
K. and others at Oracle when they had to design their database
dictionary. What you are describing is exactly the approach taken for it
(well they have not strictly speaking defined PKs as such but that's
what NOT NULL unique columns are, aren't they), ie the table of users
contains a unique NAME column but the real key is the user# column,
while in the table of objects the key is obj# even if there is a unique
index on (owner#, name). Quite commendable. The cons, you have them when
you try to execute complicated queries on the so-called 'official'
dictionary table : since what externally appears to be the key is not,
internally, you write the joins on, say, tablename and username, whereas
those values are already fetched from different tables, and it's
slooooow. If all your queries are carefully written, however, it can be
quite fast. I think you have understood my point, the problem lies, as
ever, with users.
--
Regards,
Stéphane Faroult
Oriole Corporation
 |
 |