hi,

Koala wrote:
> Navjot Singh wrote:
>
>>> This patters has several drawbacks, for example the real primary key
>>> should be defined as UNIQUE and others stuff reported in EJBDesign
>>> Pattern book.
>>
>>
>> Primary key IS UNIQUE. What's the drawback here?
>
>
> Suppose I have two entities Student and courses with the following
> attributes:
>
> Student:          Courses:
> -----------          ----------
> name             name
> surname          date
> address
>
> now (name, surname) could be a primary key for student and (name) could
> be a primary key for courses. Suppose I have 10 elements in Student
> and the size of index (for primary key) is 100 Kb.
>
> It is clear that if I organize my tables in a different way, using
> sequence block patters I could have:
>
> Student:          Courses:
> -----------          ----------
> id                 id
> name             name
> surname          date
> address
>
> where id is "effective" primary key for both the table and, to avoid to
> have duplicate "real" primary key, I have to define (name, surname) on
> Student has
> UNIQUE (the same must be done for name in Couses).
> Using this approach the size of index for primary key will be less than
> 100 Kb, since my primary key is a simple integer. This should improve
> insert/search/update commands (I hope).
ok now understand what you mean by "long indices". i thought you mean
long in length.

> The question is:
> is this approach appliable to all the entities of whatever database schema?
Not really. assume you have an application which allows the customer to
identify themselves as "username" which could be varchar(15) which is
not very bad case than int(11). In this case, i would not consider your
"id" case.

"id" has it's design benefit also that i like. Suppose you have to
change your "meaningful" primary key like username for some weird
reason. If you are using "id", you *wont* have to worry about going
around all your tables updating those FK columns that relate to this PK.

> I am working on a project where this type of technique has been used and
> I would like to know if it is a good choice.

Consedring from the purely database design issue, NO. But practically
there are cases when you this "id" surely has advantages. Take a case of
Order ----> OrderItems*

If you have to identify uniquely each record in OrderItems, there's no
easy way. Then, "id" is the way to go.

>>
>>> Now I have a design question. If I have a DB schema with a set of table,
>>> is it a good choice use a long id as primary key for all the tables?
>>> When it could be necessary use long ids as primary keys? In which cases
>>> I should avoid this?
>>
>> Wrong way to look at PKs. usually people keep one of smallest CANDIDATE
>> Keys as primary key. Many people dont worry much about them and just
>> keep auto_increment / sequence column as PK.
>>
> Yes. But do you think this is a good choice. Are there cases in which it
> is better not define a "effective" primary key (probably you call it
> CANDIDATE)
> but use the "real" primary key?

I just mentioned 1 case above.

Moreover, when your ISAM table gets corrupt and you wish to repair it,
there's no easy way to do it unless you have some UNIQ key in that
table. and "id" is great help in that :-)

hope it makes sense.
navjot

====================================================================
Companion Site: http://www.corej2eepatterns.com
J2EE BluePrints: http://java.sun.com/blueprints/corej2eepatterns
List Archive: http://archives.java.sun.com/archives/j2eepatterns-interest.html
Unsubscribing: email "signoff J2EEPATTERNS-INTEREST" to listserv@(protected)