>> Thadeus Burgess >> WRong. cr2 is a really really bad design. >> Database 101, any many to many relationship must be defined through a link >> table.
I'm sorry, but you missunderstand the term "many to many relationship" and the term "relation" at all. Because, of course, "one relation" means exactly "one relation" = one kind of relationship between two entities. In other words "one relation" has exactly ONE meaning in the real world, for which we create the model. If we realy have ONE relation, which is "many to many", then there is NO POSSIBILITY, how to design this using 2 tables, and 3-rd link table (with foreign keys to both previous) must be added. - Please think about this to understand the difference: We have here ONE relation from the real world, which we realize with TWO relations of the data model, because we have no other way how to do it. That's not the case of http://zvolsky.alwaysdata.net/crm2.jpg Here we have 3 relations which describe 3 different relationships (relationships of different reasons!) between real entities: 1) which person is an employee of which company, 2) which person has created the company record, 3) which person has created the person records, all 3 relations are "1:m" relations. To realize "1:m" relation there is no need to add a connecting table and if you do so, that would be a realy very bad design ! And it's easy to say why in other words: Because in case of real "m:m" relation there can be more as one records in the added connecting table to describe relationship between two entities. But if you will add connecting table from your weak reasons, then you will have always one(!) record in the connecting table. So you will have no benefit when you add such table, but you receive lot of additional problems to handle the more difficult model. I hope I have exlained it well with my bad english. I'm sorry that I speak a little hard, this is not from the reason that previous are basic things in relational databases and in SQL language, but reason is that there is a risk, that you will avoid Massimo to make that great change. Mirek
