[AccessD] Table design

Tim,

This is not the easiest way to implement a Many-to-Many relationship.
The easiest way is to use three tables:  one for Products, one for
OrderTypes, and a linking table to show what Products can go with what
OrderTypes.


Charles Wortz 
Software Development Division 
Texas Education Agency 
1701 N. Congress Ave 
Austin, TX 78701-1494 
512-463-9493 
CWortz at tea.state.tx.us 
-----Original Message-----
From: Pain, T. (Tim) [mailto:Tim.Pain at sc.akzonobel.com] 
Sent: Friday 2003 May 30 09:08
To: accessd at databaseadvisors.com
Subject: [AccessD] Table design

I currently have the following <snipped> table designs - 
Products: 
Product         Christmas       Easter  AllYear Major
Corporate 
Product 1               True            False           True
False           True 
Product 2               False           False           True
True            False 
Product 3               etc' 
OrderType: 
OrderType               Christmas       Easter  AllYear Major
Corporate 
Christmas               True            False           False
True            False 
Easter          False           True            False           True
False 
AllYear         False           False           True            True
False 
Corporate               True            True            True
True            True 
Major                   False           False           False
True            False 
In Order Entry, the user selects an "OrderType" in the header and I then
need to check for each line item, if this product is allowed for this
"OrderType".
Now this works, but does require quite a bit of code to test each
variation, but more important, it also makes maintenance a pain. If I
now need to add a new OrderType, I have to add a record to the OrderType
table (no problem with this), but also add a column to the Products
table and then re-work the code to check the order line items.
There has to be a more elegant and easier to maintain way of doing this,
but I can't see it. 
Any ideas please? 
Many thanks 
Tim