Subject | Re: [IBO] Design question about master (master?) detail relation |
---|---|
Author | Helen Borrie |
Post date | 2001-04-12T17:36:29Z |
At 03:13 PM 12-04-01 +0000, you wrote:
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
phonenumber
from phonenumber
where locationid = :locationid
Set its KeyLinks property to phoneid.
Set Mastersource to your customer query
Set MasterLinks to
phonenumber.customerid=customer.customerid
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
viz.
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.
Cheers,
Helen
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________
> > Marco,Marco,
> >
> > just have a look to the MasterLinks Demo in the IBO´s Sample
>directory.
> > 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
phonenumber
from phonenumber
where locationid = :locationid
Set its KeyLinks property to phoneid.
Set Mastersource to your customer query
Set MasterLinks to
phonenumber.customerid=customer.customerid
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
viz.
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.
Cheers,
Helen
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________