Normalising a Many-to-Many Relationship

[RichUE]

I am designing a Components database to analyse forecast costings of parts
based on quotes. I have two tables that appear to have a Many-to-Many
relationship, and I want to resolve this into two Many-to-Ones. My tables are:

tbl_Parts, consisting of:
txt_PartNumber (PK)
txt_PartDescription
etc.

tbl_PriceBreaks, consisting of:
txt_PartNum (FK) (PN)
int_MinimumOrderQuantity (MOQ)
cur_MinimumOrderValue (MOV)
int_PriceBreakQty (PBQ)
cur_PriceBreak (PB)

tbl_PriceBreaks example data:
PN, MOQ, MOV, PBQ, PB
1, 50, null, 50, 1.25
1, 50, null, 500, 1.10
2, null, 500.00, null, 3.50
2, 200, null, 200, 3.15

There will either be a minimum order quantity, or a minimum order value,
resulting from a particular quote, but not both. However, different suppliers
may quote using MOQ or MOV. The price break quantity may be the same as the
MOQ or may be higher depending on the quote. For a part with an MOQ of 200,
there may be a price break at 1000, for example. The price break quantity may
be calculated from an MOV, if there is one. There may be several price break
cost values depending on the quote.

A third related table is:
tbl_Quotes, consisting of:
txt_PartNumber (FK)
cur_Cost
dtm_DateOfQuote
int_QuoteLine

I evaluated the Parts-PriceBreaks relationship as Many-to-Many because each
part may have a number of price breaks, e.g. 50 off, 100 off, 500 off,...,
and similarly each price break will be associated with many parts. Do I have
too much data in tbl_PriceBreaks? How could I sub-divide it to eliminate the
Many-to-Many relationship?

 

[Sylvain Lafontaine]

From your description, you don't have a Many-to-Many relationship here
because each line (or row) of the tbl_PriceBreaks table is associated with a
single row (or part) of the tbl_Parts table.

To have (or create) a Many-to-Many relationship between the two tables
tbl_PriceBreaks and tbl_parts, you cannot have the primary key of the
tbl_Parts table as a foreign key in the tbl_PriceBreaks table. Also, when
you have a real Many-to-Many relationship, you cannot resolve it into two
Many-to-Ones relationships; so your question about asking on how to
normalise a Many-to-Many relationship is rather pointless.

BTW, it's now generally considered useless to have prefixes such as tbl_ and
txt_ but this is another story.

 

[Jeff Boyce]

Sylvain

I've run across many folks who use the object-type prefix ("tbl", "frm",
"qupd"...) to help differentiate among objects that might otherwise have
identical titles.

But then, I may be a dinosaur!

Regards

Jeff Boyce

From your description, you don't have a Many-to-Many relationship here
because each line (or row) of the tbl_PriceBreaks table is associated with
a single row (or part) of the tbl_Parts table.

To have (or create) a Many-to-Many relationship between the two tables
tbl_PriceBreaks and tbl_parts, you cannot have the primary key of the
tbl_Parts table as a foreign key in the tbl_PriceBreaks table. Also, when
you have a real Many-to-Many relationship, you cannot resolve it into two
Many-to-Ones relationships; so your question about asking on how to
normalise a Many-to-Many relationship is rather pointless.

BTW, it's now generally considered useless to have prefixes such as tbl_
and txt_ but this is another story.

 

[Keith Wilby]

BTW, it's now generally considered useless to have prefixes such as tbl_
and txt_ but this is another story.

News to me. I find prefixes invaluable and always use them and know of many
developers who also use them (against only 2 who don't, one of which is
yourself), so I guess your generalisation is a bit wide of the mark. YMMV.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

But then, I may be a dinosaur!

Not in my experience Jeff but I guess it's a personal preference thing.

Keith.