Products with accesories that exclude other accessories

[crimedog]

If I set up two tables - TblProducts One to Many TblAccessories


1.Each Product may have multiple Accessories
2. Each Accessory may be on multiple Products
3. If you add one Accessory to a product it may require an additional accessory
4. An Accessory may block a different accessory (you can add one but not both)
5. a product may Require one accessory from a list

Okay - so how to set up the fields in the table

TblProducts: PartNumber (Primary) PartDescription Price
TblAccessories: AccPartNumber Primary) PartDescription Price
How do I set up the fields for PartNumber requires AccPartNumber
AccPartNumber does not allow AccPartnumber

Or is it separate tables

sorry it is so vague - but I am just thinking it through.

[CJ_London]

you are describing a many to many relationship - which to handle you need a joining or linking table which at its simplest contains two fields - a family key link to tblProducts and another family key to tblaccessories

with regards requirements 3-5, this would be down to business rules which you would store in the relevant product or accessory table.

your rules aren't very clear (to me at least) so regards rule 4 that would perhaps have an 'exclusive' flag in the accessories table which prevents other accessories being added. Rule 5 might be managed by having a 'maxaccessories' field in the products table - 0 mean no limit, 1 means only 1 etc.

[crimedog]

If I have the Many to Many - and there are 10 of the same accessories on 5 of the products - does that mean the bridge form has 50 records? or is there a way to set 10 records

[CJ_London]

does that mean the bridge form has 50 records?

if you have 5 products which each have 10 accessories, you would have 50 records

tblProdAcc
ProductFK long
AccessoryFK long

ProductFK..AccessoryFK
1..............10
1..............11
1..............22
2..............10
3..............10
3..............22

this shows product 1 has 3 accessories, product 2 has 1 and product 3 has 2.

all 3 products use accessory 10, whilst accessory 22 is used on products 1 and 3 and accessory 11 is only used on product 1