Minimize Many-to-Many

[hi there]

howdy all,

i have kinda a general rule of thumb question. is it generally a good design approach to limit the number of many-to-many relationships in an application. i'm finding that making entry forms that are easy for users to fill out is much more complicated when using many-to-many recordsources. i generally like to make my users forms 75% predefined lists (e.g. combo boxes, list boxes, radio buttons,..etc). doing this for many to many stuff can get hairy because of all the possible combinations. for example if i have a many-to-many relationship between tblSource and tblArea i can say that it takes a unique combination of source and area to make a unique record because one area can have many sources, but one source can be located in several areas. a lot of times i'm finding that sometimes i can avoid a many-to-many relationships by just including one of the tables as a lookup field in another table. using the above example scenario, i could include area info as a field in the table tblEquipment. so now the relationship would be tblSource > tblEquipment with a lookup field to tblArea.

is there any general rule of thumb for setting these types of relationships.

sorry if the above text makes absolutely no sense.

thanks

 

[jeremie_ingram]

Generally speaking from what I have learned and experienced, Many-to-Many relationships are a big no no.

If you find that you have a many to many going on, try creating a junction table. Take the primary key from each of the tables and make them the foriegn key in one table.

tbl1
TBL1PK
TBL1Info
TBL1Info2

tbl2
TBL2PKEY
TBL2Info
TBL2Info2

tblJunction
TBLJUNCPKEY
TBL1PKEY
TBL2PKEY

You could add as many pkeys as neccessary to create 1 tbl that make up unique records.

 

[nitherwise]

How would you get the records from the two tables into one table by just using the form?

I've been trying to figure this out to no avail. Please help.

Thanks

 

[jeremie_ingram]

Since the tables will be linked via relationships between the Primary and foriegn keys, you can easily base a form or report off of a query. The query would contain all of the tables, and the relatioships are established, so then you can add the necessary fields.


From my previous example, you would add all three tables into the query. Add the fields (excluding the PKeys) to the query. When it is run all of the data is shown as one source although it comes from many sources.

 

[hi there]

hi jeremie,

thanks for the response. when required i do use junction tables to create many-to-many relationships as you described. depending on whether the junction table ends up being a parent in a one-to-many relationship i either use an autonumber PK or use the two FK's as a compound PK. the problem i'm have is creating a nice clean entry form based on the many-to-many relationship. users end up having to select from a huge list of combinations to populate the FK's on the entry form. so the many-to-many relationship itself is kinda the problem. i'm thinking my question was really more of a when to normalize and when not to normalize question. i'm wondering if in some cases i can get out of many-to-many relationships by using lookup fields with the appropriate update queries when changes occur.

thanks

 

[hi there]

duh, i don't know what i was thinking about combinations. you're completely right about the 2 separate combo boxes for the FK's on the entry form. my next issue will be can i use memo fields in my combo box? i think i might need to change some data types in my tables. as a matter of fact i used your demo a while back to figure out how many-to-many relationships worked in the first place. i think what spawned this question was i am worried that some of normalization schemes are conflicting with business rules and making things overly complex. for some reason, many-to-many just doesn't seem nearly as clean as one-to-many.

thanks for the help pat.

 

[hi there]

i wasn't going to join the tables based on memo fields or long text fields however users will need the information in these fields to make their selection in the combo boxes which populate the many-to-many FK's. the information in these memo fields cannot be abridged because it containes legal language which must be copied verbatim from the hard copy document.

for example

the combo box might have 2 bound columns (1 PK, memo) from the underlying table. users should see the memo field, make their selection, and the control will store the PK, however i didn't think you can put memo fields in combo boxes.

 

[hi there]

i think using a summary field is the way to go. as always thanks for all the help pat.