John Ruff
papparuff at attbi.com
Fri May 30 10:10:15 CDT 2003
Here's a small diagram.
tbl_Product tblk_ProdType tbl_Type
|----------| |--------| |----------|
| ProdID |------->| ProdID | |---| TypeID |
| | | TypeID |<---| | Desc |
|----------| |--------| |----------|
John V. Ruff - The Eternal Optimist :-)
"Commit to the Lord whatever you do,
and your plans will succeed." Proverbs 16:3
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pain, T.
(Tim)
Sent: Friday, May 30, 2003 8:00 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Table design
Charles,
That's sort of what I figured, but I can't get my head round how the
linking table should be structured. Is this one record for each Product,
OrderType combination, or something else?
Many thanks
Tim
-----Original Message-----
From: Wortz, Charles [mailto:CWortz at tea.state.tx.us]
Sent: 30 May 2003 15:24
To: accessd at databaseadvisors.com
Subject: RE: [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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com