ManyToManyRelationship

[Jeff]

I am using Access 2007 and we are redesigning a database application.
Everything is done except for the way the client would like the prices for
items stored. Below are the 3 tables and structures we have come up with so
far.

Items *...........* ItemStyles *.......................* ItemPrices
ItemID ItemStyleID ItemPriceID
ItemPCode ItemID ItemID
Description
ItemStyleID
Associated ItemStyleID Price

There can be from 1 to many ItemStyles. Normally there are 12. The
combination of ItemStyleID and ItemID determines the price. Since Various
ItemStyles have the exact same ItemID' s and Prices we want to only store
the price once.

Example there are 5 ItemStyles (IS1, IS2, IS3, IS4, IS4)

IS1 and IS3 have the exact same prices so we want to only have 1 record for
each ItemStyleID\ItemID combination in the ItemPrices Table for IS1 and IS3.

IS2, 4, and 5 all have different prices so they each have separate record
for there prices. If they decide to change IS3 prices then we need to be
able to have the ItemPrice table updated, as well as if they decided to make
the prices for IS5 be the same as IS1, then they would need to become one
entry in the itemprice table.

What we came up with was to add an AssociatedItemStyleID field to the
ItemStyle table and any ItemStyles sharing the same price structure would be
flagged here. One problem is how to get these to be displayed in a form for
the user to change or add prices. I was thinking of a view for this, but
not sure. Another is how to get the adding\updating in the ItemPrice table
to work. If there is a better solution for the tables, any input would be
appreciated.

TIA

 

[Allen Browne]

Okay, you say:
The combination of ItemStyleID and ItemID determines the price
Various ItemStyles have the exact same ItemID' s

How about:
- a DefaultItemPrice fields in your Items table.
- an ItemStylePrice in the ItemStyles table.
If the particular combination of ItemStyleID + ItemID is the default price,
you leave the ItemStylePrice blank.

Then in a query, the price of an item is:
ActualPrice: IIf([ItemStylePrice] Is Null, [DefaultItemPrice],
[ItemStylePrice])

 

[Jeff]

Allen:

This would work, except when they wanted to add a ItemStyle or remove an
ItemStyle, they would have to have the Items table structure modified. That
is what they are trying to avoid. This is similar to the old program. They
had 14 ItemStyles and in the Item table they had a column for the Item and a
column for each Style. When they discontinued a Style or wanted to add a
new one, they had to have it done by a programmer. That is why we created
the a table for the combination of the ItemStyleID and ItemID with the
price. We thought it would be easier to manage the data.

Thanks

Okay, you say:
The combination of ItemStyleID and ItemID determines the price
Various ItemStyles have the exact same ItemID' s

How about:
- a DefaultItemPrice fields in your Items table.
- an ItemStylePrice in the ItemStyles table.
If the particular combination of ItemStyleID + ItemID is the default
price, you leave the ItemStylePrice blank.

Then in a query, the price of an item is:
ActualPrice: IIf([ItemStylePrice] Is Null, [DefaultItemPrice],
[ItemStylePrice])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2007 and we are redesigning a database application.
Everything is done except for the way the client would like the prices
for items stored. Below are the 3 tables and structures we have come up
with so far.

Items *...........* ItemStyles *.......................* ItemPrices
ItemID ItemStyleID ItemPriceID
ItemPCode ItemID ItemID
Description ItemStyleID
Associated ItemStyleID Price

There can be from 1 to many ItemStyles. Normally there are 12. The
combination of ItemStyleID and ItemID determines the price. Since
Various ItemStyles have the exact same ItemID' s and Prices we want to
only store the price once.

Example there are 5 ItemStyles (IS1, IS2, IS3, IS4, IS4)

IS1 and IS3 have the exact same prices so we want to only have 1 record
for each ItemStyleID\ItemID combination in the ItemPrices Table for IS1
and IS3.

IS2, 4, and 5 all have different prices so they each have separate record
for there prices. If they decide to change IS3 prices then we need to be
able to have the ItemPrice table updated, as well as if they decided to
make the prices for IS5 be the same as IS1, then they would need to
become one entry in the itemprice table.

What we came up with was to add an AssociatedItemStyleID field to the
ItemStyle table and any ItemStyles sharing the same price structure would
be flagged here. One problem is how to get these to be displayed in a
form for the user to change or add prices. I was thinking of a view for
this, but not sure. Another is how to get the adding\updating in the
ItemPrice table to work. If there is a better solution for the tables,
any input would be appreciated.

 

[Allen Browne]

So you have no current field for price anywhere, and you don't want to
change the existing tables at all. Ok

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen:

This would work, except when they wanted to add a ItemStyle or remove an
ItemStyle, they would have to have the Items table structure modified.
That is what they are trying to avoid. This is similar to the old
program. They had 14 ItemStyles and in the Item table they had a column
for the Item and a column for each Style. When they discontinued a Style
or wanted to add a new one, they had to have it done by a programmer.
That is why we created the a table for the combination of the ItemStyleID
and ItemID with the price. We thought it would be easier to manage the
data.

Thanks

Okay, you say:
The combination of ItemStyleID and ItemID determines the price
Various ItemStyles have the exact same ItemID' s

How about:
- a DefaultItemPrice fields in your Items table.
- an ItemStylePrice in the ItemStyles table.
If the particular combination of ItemStyleID + ItemID is the default
price, you leave the ItemStylePrice blank.

Then in a query, the price of an item is:
ActualPrice: IIf([ItemStylePrice] Is Null, [DefaultItemPrice],
[ItemStylePrice])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am using Access 2007 and we are redesigning a database application.
Everything is done except for the way the client would like the prices
for items stored. Below are the 3 tables and structures we have come up
with so far.

Items *...........* ItemStyles *.......................*
ItemPrices
ItemID ItemStyleID ItemPriceID
ItemPCode ItemID ItemID
Description ItemStyleID
Associated ItemStyleID Price

There can be from 1 to many ItemStyles. Normally there are 12. The
combination of ItemStyleID and ItemID determines the price. Since
Various ItemStyles have the exact same ItemID' s and Prices we want to
only store the price once.

Example there are 5 ItemStyles (IS1, IS2, IS3, IS4, IS4)

IS1 and IS3 have the exact same prices so we want to only have 1 record
for each ItemStyleID\ItemID combination in the ItemPrices Table for IS1
and IS3.

IS2, 4, and 5 all have different prices so they each have separate
record for there prices. If they decide to change IS3 prices then we
need to be able to have the ItemPrice table updated, as well as if they
decided to make the prices for IS5 be the same as IS1, then they would
need to become one entry in the itemprice table.

What we came up with was to add an AssociatedItemStyleID field to the
ItemStyle table and any ItemStyles sharing the same price structure
would be flagged here. One problem is how to get these to be displayed
in a form for the user to change or add prices. I was thinking of a
view for this, but not sure. Another is how to get the adding\updating
in the ItemPrice table to work. If there is a better solution for the
tables, any input would be appreciated.

 

[John W. Vinson]

Allen:

This would work, except when they wanted to add a ItemStyle or remove an
ItemStyle, they would have to have the Items table structure modified. That
is what they are trying to avoid. This is similar to the old program. They
had 14 ItemStyles and in the Item table they had a column for the Item and a
column for each Style. When they discontinued a Style or wanted to add a
new one, they had to have it done by a programmer. That is why we created
the a table for the combination of the ItemStyleID and ItemID with the
price. We thought it would be easier to manage the data.

Reread Allen's excellent and accurate suggestion. He is suggesting adding A
NEW RECORD to the ItemStyles table when a new style is added (with an optional
field for that style price). He is *NOT* suggesting changing the structure of
the table.

His suggestion will work, and does meet your objection.

 

[Jeff]

John:

That would be ok if there was only 1 price for an ItemStyle, but there can
be 800 or 900 prices per ItemStyle.

ItemPriceTable

ItemID ItemStyleID ItemPrice
1 1 1.00
1 2 2.00
1 3 3.00
1 4 1.00
1 5 4.00
2 1 5.00
2 3 2.50
2 4 5.00
.......



Given the above data ItemStyleID 1 and 4 would share the same ItemID' s and
prices. So instead of having ItemSyleID 1 and 4 we would only want
ItemStyle1 with a reference to ItemStyle4.

The old table was setup like below:

ItemID ItemStyle1 ItemStyle2 ItemStyle3 ItemStyle4 ItemStyle5
1 1.00 2.00 3.00 1.00
4.00
2 5.00 0 2.50
5.00 0

.....


Is this clearer or am I not understanding the suggestion?

Thanks

 

[John W. Vinson]

John:

That would be ok if there was only 1 price for an ItemStyle, but there can
be 800 or 900 prices per ItemStyle.

ItemPriceTable

ItemID ItemStyleID ItemPrice
1 1 1.00
1 2 2.00
1 3 3.00
1 4 1.00
1 5 4.00
2 1 5.00
2 3 2.50
2 4 5.00
......



Given the above data ItemStyleID 1 and 4 would share the same ItemID' s and
prices. So instead of having ItemSyleID 1 and 4 we would only want
ItemStyle1 with a reference to ItemStyle4.

The price depends on *two inputs*: the ItemID and the ItemStyleID. Neither ID
by itself identifies a price; you need both.

Given that, once you know the ItemID, and the ItemStyleID, you can look up the
unique single price for that combination.

When the price changes, you find the row for that combination and change it;
when you get a new style for an item, you add a new row to the table for that
combination.

I simply do not understand your concern.

 

[Jeff]

John:

In order to cut down on user input, the client wants us to display the data
in the form, using a table. If there are more than 1 ItemStyle that have
the same ItemID' s and Prices, then they would like to display them in the
same column, with the heading listing the Item Styles. So what I was
wondering was there a better way to store the data to accomplish this. The
heading is not an issue. Also if there are more than one ItemStyle with
the same ItemID' s and Price, why store them twice?

Given the data below it should show up on the fomr like this:


ItemStyle4
ItemID ItemStyle1 ItemStyle2 ItemStyle3 ItemStyle5
1 1.00 2.00 3.00 4.00
2 5.00 0 2.50 0



Thanks for all the input.

 

[John W. Vinson]

John:

In order to cut down on user input, the client wants us to display the data
in the form, using a table. If there are more than 1 ItemStyle that have
the same ItemID' s and Prices, then they would like to display them in the
same column, with the heading listing the Item Styles. So what I was
wondering was there a better way to store the data to accomplish this. The
heading is not an issue. Also if there are more than one ItemStyle with
the same ItemID' s and Price, why store them twice?

Given the data below it should show up on the fomr like this:


ItemStyle4
ItemID ItemStyle1 ItemStyle2 ItemStyle3 ItemStyle5
1 1.00 2.00 3.00 4.00
2 5.00 0 2.50 0

How do you want it displayed if you have 32 styles for an item? Scroll bar?
Really really wide screen?

You can certainly use a Crosstab query to reorganize the tall-thin normalized
table in this wide-flat format, but the result will of course not be editable.

As for the storage, you're making a false economy. You have two real-life
facts to store: the fact that Item 3, Style 4 costs $4.00, and the (totally
separate, unrelated!!) fact that Item 11, style 2 costs $4.00. The fact that
the two dollar values happen (at the moment, pending tomorrow's price change)
to be the same is irrelevant: you have two facts, you should store them both.
Wasting 8 bytes for a Currency field pales against the cost in the greatly
increased complexity of your application.