customer.name joining transactions.name vs. customer.id [serial] joining transactions.id [integer]
- by Frank Computer
INFORMIX-SQL 7.32 Pawnshop Application:
one-to-many relationship where each customer (master) can have many transactions (detail).
customer(
id serial,
pk_name char(30), {PATERNAL-NAME MATERNAL-NAME, FIRST-NAME MIDDLE-NAME}
[...]
);
unique index on id;
unique cluster index on name;
transaction(
fk_name char(30),
ticket_number serial,
[...]
);
dups cluster index on fk_name;
unique index on ticket_number;
Several people have told me this is not the correct way to join master to detail. They said I should always join customer.id[serial] to transactions.id[integer].
When a customer pawns merchandise, clerk queries the master using wildcards on name. The query usually returns several customers, clerk scrolls until locating the right name, enters a 'D' to change to detail transactions table, all transactions are automatically queried, then clerk enters an 'A' to add a new transaction.
The problem with using customer.id joining transaction.id is that although the customer table is maintained in sorted name order, clustering the transaction table by fk_id groups
the transactions by fk_id, but they are not in the same order as the customer name, so when clerk is scrolling through customer names in the master, the system has to jump allover the place to locate the clustered transactions belonging to each customer. As each new customer is added, the next id is assigned to that customer, but new customers dont show up in alphabetical order. I experimented using id joins and confirmed the decrease in performance.
How can I use id joins instead of name joins and still preserve the clustered transaction order by name if transactions has no name column?
