Re: foreign key references only part of composite PK - table scan issue

On Aug 8, 9:30 pm, bill <billmacle...@xxxxxxxxx> wrote:
Are you allowed to add to the schema and do any redesign?

It sounds to me like table ABC is actually a relationship between two
missing tables (call them table 1 and table 2, after your column
names).  If DEF needs a foreign key to only column 1, then there
should be a table whose primary key is just column 1.  You also need a
table whose primary key is just column 2.  Those two tables have a
many-to-many relationship that is resolved by table ABC.

Table DEF should be a child of table 1, and table MNO should be a
child of table 2.  Neither DEF nor MNO should reference table ABC.

As I understand it from what what you wrote about the keys, adding
these two tables is the real answer.  If you find that your
application has queries against ABC that use the DISTINCT keyword or a
GROUP BY clause, that would even be more confirmation that you need
the new tables.

Thanks,

Bill

yeah the basis of you idea is good (small typo column names i think
though), sort of a variation to my suggested relation/join tables
(table between ABC, and DEF with fields: fieldID3, fieldID1,
fieldID2).

i may be leaning towards adding fieldID2 to DEF, and adding a child
table to DEF - ChildDEF - with fields fieldID1, fieldID2, and non
unique index on fieldID2. this would allow for 1-many relationship
(between fieldID1 and fieldID2) and allow joining to both fields of
the composite PK in table ABC (avoiding table scans).

not a perfect solution but maybe best so far?

thanks, dave
.

Relevant Pages

  • Re: A subquery? Not sure if this is doable?
    ... the weekly series and the repeating monthly figure. ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: Classes as units of reuse
    ... Component ABC { ... The thing that is not clear in your pseudo code is the nature of the relationship between ABC and DEF. ... IMO, the second approach would be the best where one abstracts some set of objects with the proper functionality and then decouples their details from the rest of the application via a Facade interface to which the clients all talk. ... In the OO paradigm one basically has three levels of logical indivisibility: subsystem, object, and responsibility. ...
    (comp.object)
  • Re: A subquery? Not sure if this is doable?
    ... frequency such that the table with less frequency will repeat its value ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: A subquery? Not sure if this is doable?
    ... my computer hangs and hangs and hangs. ... 1/12/05 ABC 4.22 ... 1/12/05 DEF 3.12 ... 1/12/05 XYZ 8.88 ...
    (microsoft.public.access.queries)
  • Re: Linking sort results
    ... ABC 123 ... DEF 234 ... to cater for the max likely rows of data in cols A and B ... except that it points to col E in Sheet1 ...
    (microsoft.public.excel.worksheet.functions)
Loading