Subject Re: [IBO] Design question about master (master?) detail relation
Author Helen Borrie
At 03:13 PM 12-04-01 +0000, you wrote:

> > Marco,
> >
> > just have a look to the MasterLinks Demo in the IBO´s Sample
> > That looks exactly like what you trying to do.
> >
> > Harald
>I don't think so. MasterLinks Demo is a master(a) detail(b) detail(c)
>problem, where (b) is, alone, the master for (c).
>In my situation (c) is detail of (b) PLUS (a), since the (c) table
>unique index and foreign key is based on fields in (a) PLUS fields in
>(b). I think that the better solution could be redesign my database
>structure to bring it to master detail detail problem... You did not
>answer my question but gave me a better way of thinking...

Back to my old warhorse, low selectivity, Table b shouldn't be a detail table, it should be a lookup.

create table phonelocation
(locationid integer not null primary key,
location varchar(10));

create table phonenumber
(phoneid integer not null primary key,
customerid integer not null,
locationid integer not null,
phonenumber varchar(18),
foreign key(customerid) references customer(customerid)

create unique index phoneitem_u on phonenumber(customerid,locationid,phonenumber);

You would certainly want to avoid a foreign key relationship between phonelocation and customer, because phonelocation has extremely low selectivity (only four possible values in the whole customer database).

You can get these into a lookup dataset simply enough:

select locationid, location from phonelocation // hide the locationid

A simple query gets the detail dataset for the selected customer and location:

select phoneid, //hide
customerid, //hide
locationid, // hide
from phonenumber
where locationid = :locationid

Set its KeyLinks property to phoneid.
Set Mastersource to your customer query
Set MasterLinks to

Because this will be a very small dataset, you could possibly gain by not using the parameter to restrict the locationid. You could set Filtered to True and apply a run-time filter to it on the lookup dataset's OnDataChange event
detailqry.Filter := QuotedStr('locationid = ' + IntToStr(lookupqry.Fields['locationid'].Value));

You'll get an empty dataset if the customer has no phone numbers for the selected location.


All for Open and Open for All
InterBase Developer Initiative ·