Hi All,
I am about to make some changes to a process we have. The new process
will have a new table who's job it is to store temporarily data such
as customer, clerkno,productno, storeno , amount, time.

On a new request from a front-end i will select against the primary
keys to ensure no other request for the same data has been made. This
is then sent to a backend system asynchronously if there is no match.
I will otherwise send a reject msg back to the store if i have the
same data in the table.

On the reply from the backend, i delete this record.
Now this table will hardly ever be filled as messages will be stored
and deleted within a second.

My question is, should i create this table with a single primary key
such as a sequence number or should it be a composite key of say
(customer, clerkno,productno, storeno).
I ask because, i am reluctant to have composite if the database is
maintaining indexes against each primary key column. I.e. four indexes
will have be be maintained for each insert or delete.

Should i just go with a single column sequence pk and then i just
index my customer column for a more restrictive select statement. Any
suggestions?

Thanks,

"lfhenry" <lfhenry@gmail.com> wrote in message
news:c586846f-c0e7-487c-a789-ab88807d4c10@8g2000hsu.googlegroups.com...
> Hi All,
> I am about to make some changes to a process we have. The new process
> will have a new table who's job it is to store temporarily data such
> as customer, clerkno,productno, storeno , amount, time.
>
> On a new request from a front-end i will select against the primary
> keys to ensure no other request for the same data has been made. This
> is then sent to a backend system asynchronously if there is no match.
> I will otherwise send a reject msg back to the store if i have the
> same data in the table.
>
> On the reply from the backend, i delete this record.
> Now this table will hardly ever be filled as messages will be stored
> and deleted within a second.
>
> My question is, should i create this table with a single primary key
> such as a sequence number or should it be a composite key of say
> (customer, clerkno,productno, storeno).
> I ask because, i am reluctant to have composite if the database is
> maintaining indexes against each primary key column. I.e. four indexes
> will have be be maintained for each insert or delete.
>
> Should i just go with a single column sequence pk and then i just
> index my customer column for a more restrictive select statement. Any
> suggestions?
>
> Thanks,

If you a composite primary key of (customer, clerkno,productno, storeno) DB2
will only create one index for this.

On Mar 22, 12:17*pm, "Mark A" <nob...@nowhere.com> wrote:
> "lfhenry" <lfhe...@gmail.com> wrote in message
>
> news:c586846f-c0e7-487c-a789-ab88807d4c10@8g2000hsu.googlegroups.com...
>
>
>
>
>
> > Hi All,
> > I am about to make some changes to a process we have. The new process
> > will have a new table who's job it is to store temporarily data such
> > as customer, clerkno,productno, storeno , amount, time.
>
> > On a new request from a front-end i will select against the primary
> > keys to ensure no other request for the same data has been made. This
> > is then sent to a backend system asynchronously if there is no match.
> > I will otherwise send a reject msg back to the store if i have the
> > same data in the table.
>
> > On the reply from the backend, i delete this record.
> > Now this table will hardly ever be filled as messages will be stored
> > and deleted within a second.
>
> > My question is, should i create this table with a single primary key
> > such as a sequence number or should it be a composite key of say
> > (customer, clerkno,productno, storeno).
> > I ask because, i am reluctant to have composite if the database is
> > maintaining indexes against each primary key column. I.e. four indexes
> > will have be be maintained for each insert or delete.
>
> > Should i just go with a single column sequence pk and then i just
> > index my customer column for a more restrictive select statement. Any
> > suggestions?
>
> > Thanks,
>
> If you a composite primary key of (customer, clerkno,productno, storeno) DB2
> will only create one index for this.- Hide quoted text -
>
> - Show quoted text -

Great, composite key it is.
Thanks.