Phil :
What the ItemPriceLink table is modelling is a many-to-many relationship type
between ItemTable and PriceTypeTable, which it does by resolving the
relationship type into two on-to-many relationship types. So you are quite
correct, it does have two 'parent' tables. I prefer the terms 'referenced
table' and 'referencing table' to the more folksy 'parent table' and 'child
table' as I think the former better reflect the role of the tables in the
relational model. One thing to understand about a table which models a
relationship type in this way is that a relationship type is really just a
special kind of entity type, to the table can have other non-key columns
modelling attributes of the entity type, such as Value in your example, in
the same way that a less abstract entity type can have attributes, such as
the Description type of the Items entity type. This is easy to visualize as
each item is a physical entity, whereas a relationship between and items and
a price modelled by a row in ItemPriceLink is a more abstract entity, but an
entity nevertheless.
The level of complexity here is not really 'added' in the sense that its an
optional extra; it simply reflects the level of complexity which exists in
the real world entity types and the relationship between them. The model is
spot on for this situation.
When it comes to deleting rows from tables such as the ItemPriceLink table, I
agree with John that joins should be avoided and subqueries used to determine
which rows are to be deleted. John's given you an example in the context of
your first post. Another way, which you might find easier to reproduce in
other similar contexts would be to use two subqueries, one on each referenced
table, and to apply the EXISTS predicate to each of these. Taking a simple
example, lets say you wanted to delete rows from ItemPriceLink for item 'Box'
where the price description is 'Wholesale' (this can only be one row in this
example but the principle would exactly the same for criteria which result in
multiple rows being deleted), then a suitable query would be:
DELETE *
FROM ItemPriceLink
WHERE EXISTS
(SELECT *
FROM ItemTable
WHERE [Item Description] = "Box"
AND ItemTable.[Item#] = ItemPriceLink.[Item#])
AND EXISTS
(SELECT *
FROM PriceTypeTable
WHERE [Price Description] = "'Wholesale'"
AND PriceTypeTable .[PriceType#] = ItemPriceLink.[PriceType#]);
If we look at this in the context of your dummy data then the first subquery
identifies those rows in ItemPriceLink where there EXISTS at least one row in
ItemTable with an Item Description value of 'Box' and the same item number as
the row in ItemPriceLink, i.e. the following rows in the latter:
1 1 $4.00
1 2 $8.00
1 3 $6.00
The second subquery identifies the following rows:
1 1 $4.00
2 1 $1.00
What is actually happening here is that the EXISTS predicate is evaluating as
TRUE for these rows in each case. As the query uses a Boolean AND operation
in its WHERE clause then the WHERE clause as a whole will only evaluate to
TRUE where *both* instances of the EXISTS predicate evaluate to TRUE, which
in this case is for the row:
1 1 $4.00
this being the only one which is identified by both instances of the EXISTS
predicate. This rows is of course the one for the wholesale price for the
box item.
While I've hard-coded the criteria into each of the subqueries above in an
application you'd be more likely to use references to controls on a dialogue
form as parameters.
You might in fact have spotted that for the above example the same thing
could be done without the subqueries at all by means of parameters which
reference combo boxes on a dialogue form whose values are Item# and
PriceType#, but which list the Item Description and Price Description values
from ItemTable and PriceTypeTable by hiding the controls' bound columns in
the usual way. So the delete query would simply be:
DELETE *
FROM ItemPriceLink
WHERE Item# = Forms!MyForm!cboItem
AND PriceType# = Forms!MyForm!cboPriceType;
but, while in this case it can be very much simplified in this way, that's
only because the example is a very simple one intended to illustrate the
underlying principles. With more complex criteria it would not be possible
to simplify it in this way, but it would not be difficult to deal with an
increase in the complexity of the criteria in the subqueries.
Ken Sheridan
Stafford, England
Phil said:
What exactly is a "child" table and "Parent" table in Access? I
undersstand the concept, but not the definition as Access sees it. Here
is the problem I tend to run into a lot. A lot of the pices I work with
use THREE tables, An example follows:
Item Pricing.
ItemTable contains
Item#, Item Description.
1 box
2 bag
3 basket
PriceTypeTable Contains
PriceType#,Price Description
1 Wholesale
2 MSRP
3 StandardDiscount
ItemPriceLink Contains
Item# PriceType# Value
1 1 $4.00
1 2 $8.00
1 3 $6.00
2 1 $1.00
2 2 $2.00
2 3 $1.50
So to see all the wholesale prices, I need to reference those threee
tables. The prices are kept in ItemPriceLink, but WHICH price it is is
kept in PriceTypeTable.
A lot of the database I am working with is built this way. I see the
advantage of this type of design. If you want to add another price
level, say "International" or "Employee Price", is is as simple as
adding a record to PriceTypeTable, at which you have a new price you can
add to whatever items you choose.
But, it adds a level of complexity to the whole thing. It results is,
(conceptually at least,) Two parent tables for each child. Without both
ItemTable and PriceTypeTable, ItemPriceLink is useless.
Any insight you can give to this problem?
Phil
In access, the relationships between the tables (Referential integrity)
impacts the order for deleting records.
[quoted text clipped - 34 lines]
