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

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
.

Relevant Pages

  • Re: foreign key references only part of composite PK - table scan issue
    ... It sounds to me like table ABC is actually a relationship between two ... should be a table whose primary key is just column 1. ... Table DEF should be a child of table 1, and table MNO should be a ... i may be leaning towards adding fieldID2 to DEF, ...
    (comp.databases.ms-sqlserver)
  • 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)
Loading