Some things you are so used to thinking about one way, that to think about it another way IS considered to be "thinking outside the box". Even if you were thinking about it wrongly before, and now are thinking about it correctly.

This happened to me sorta. It was more of a case of not making the obvious conclusion about something. All the facts were presented, sugar coated, with arrows pointing to a conclusion, but I only came to it through trial and error last night.

Databases are merely containers for data, and we have to use the tools that they provide (foreign keys, primary keys, indexes, unique constraints, SQL statements, etc) to really find out the meaning of the universe. Well, I didn't get to that point yet, but it was something close. The dreaded "many to many relationship" problem. I'll explain.

The simplest way to put it is that a many to many relationship is when one single object (we'll call it Object A) has many of some other object (Object B), but one of Object B can "be in" many of Object A. The best real world example I can give, using my "Library" model on stringed.org, is Book (Object A) and Word (Object B). A book will have many words in it, and a single word can "be in" many books (and in the case of Words, it can be in a book many times).

The simplest way to portray this in a database is having a Book table and a Word table, and then you have a "link" table (probably called BookWord) that has foreign keys to both a Book and a Word.

The way "dumb" was originally created, I had no functionality to handle "many to many" relationships. I had thrown in some XML to be able to handle it in the future, but never did anything with it. (Originally, I'd get lists of the "link table" object, with the references loaded, and then populate the list that was part of "Object A" with the list populated full of "Object B" objects, all manually, everytime I needed it. My News object has many categories, and a category has many News, so I'd get a list of the link table "NewsCategoryNews", and then grab the "Category" part of that object, and my News object actually had a list of "NewsCategoryNews" objects, instead of simply a list of "Categories", which it should).

Then last night, as I was creating a control and adding functionality to potentially handle "many to many" relationships, it dawned on me. With previously created tools in "dumb" (namely a MappedListProperty which says a certain object has a list of objects, as an Author can have many books [a one to many relationship unless you include functionality to have many authors write a book, which the model doesn't include]), the conclusion was obvious. A book has a list of Words!! I know, it doesn't seem brilliant, but now it all works. See, even if they are many to many, a book still just has a list of words, and a word has a list of books that it is in. All I had to do was simply add functionality to handle this, which basically meant to add some attributes to a MappedListProperty XML node, and override some functionality used when saving and loading a ListProperty. It was easy.

Now, a News object can have a list of "Categories", instead of a list of "NewsCategoryNews" (the link object). "NewsCategoryNews", the table and the object, still have to exist, however, because "dumb" still uses it to populate the "Categories" list for a News object.

I don't have that latest uploaded yet, but I will tonight. It works beautifully. Right now I have the model set as "Books can have many Genres" (a genre is like "Suspense", "Horror", etc), and "A Genre can have many books". All I do is specify, in books, and actually in the "Genres" list property, is that a book can have many Genres, but also a Genre can "be in" many books. I specify the "link table" to use for the many to many relationship, and it's smooth sailing after that.

So to review: Many to many relationships cannot be handled with just 2 tables. You need 3. The two tables to be linked, and the link table. So to represent it in software, you just need to specify that there's a link table, and handle it accordingly. Side effect: Never write unnecessary code again :)