Re: Discrete PK's on one-to-zero-or-one children tables

some relational database management systems and data modeling tools require any foreign key to be based on a primary key in the foreign table. since your invoice table has a foreign key to local and long distance tables, each of these tables would then have to have primary keys for the invoice table to have a foreign key. in fact, based on the structure as you have described it, technically, the invoice table is a child of both LD and LOCAL, since a foreign key is found in a child table which refers to the parent table's primary key.

"Chapman Keyes" <chapman_at_teoco.com> wrote in message news:a80c63.0108090838.81c235a_at_posting.google.com...
> Greetings!
> Our standard practice has been to create a PK on every table we
> create. However, it has occurred to me to question that practice in
> certain cases. Let's say we have table INVOICE which holds telecom
> invoice data. If an invoice is long-distance, we will have one row in
> the child LD table, and none in the child LOCAL table, and vice versa
> for local invoices. We may also have an invoice with both, which will
> result in one row in both LOCAL & LD.
> We always lookup to the children based on the INVOICE FK; we
> never perform lookups based on the child PK's; you can never have a
> child record without a parent record; and none of the child tables
> will ever have children of their own.
> So, is there any advantage to creating a PK for the child tables?
> It seems like extra clutter and wasted index space, and I am inclined
> right now just to create all my one-to-zero-or-one tables with the
> parent table FK as the PK. Thanks!
Received on Mon Aug 13 2001 - 02:30:33 CEST