C#: How to model a Many to many-relationship in code?

Question

Suppose I have 2 tables in a database. eg: Dog & Boss This is a many to many relationship, cause a boss can have more than 1 dog, and a dog can have more than 1 owner. I am the owner of Bobby, but so is my wife.

But many to many is not allowed, so there is a helpertable: DogsPerBoss

How to modle this in code?

Class Boss can have a collection of Dogs. Class Dogcan have a collection of Bosses. --> at least, that is what I think. Perhaps there are better solutions?

How about extra data that is in the helper-table? Should that be in de Boss-class or in the Dog-class? eg: Nickname (I call the dog "good boy" and my wife calls him "doggie")

I hope my question is kinda clear? Are there any best-practices on what is the best way to achieve this? Can you give me some references?

An ORM (like NHibernate) is not an option.

thanx in advance.

Answer 1

If you didn't need to record the nickname, then Dog should have a list of Bosses and Boss should have a list of Dogs.

If the relationship between Dog and Boss has attributes, in this case nickname, then you should create a class to represent that relationship and have Dog and Boss both hold lists of that type.

I've been using NHibernate for a while now and find it very useful for easing this sort of object relational impedance mismatch.

Answer 2

Not sure on what your asking for. But this is the table structure you want:

Dog Table

DOG_ID int PK DOG_Name varchar(50)

DogsPerBoss

ID int DOG_ID int BOSS_ID int DogNickName varchar(15)

Boss

BOSS_ID int PK BOSS_Name varchar(50)

Answer 3

Why are you talking about tables? Are you creating an object model or a database model?

For an object model, there's no reason a Doc can't have a List<Owner> and an owner have a List<Dog>. Only if you have attributes on the relationship do you need an intermediate class (what UML calls an Association Class). That's when you'd have a DogOwnerShip class with extra properties, and each Owner would have a List<DogOwnerShip>, and so would each Dog. The DogOwner would have a Dog, an Owner, and the extra properties.

Answer 4

I guess am missing something. Why is many to many not allowed?

public class Boss
{
 Dog[] dogs;
}

public class Dog
{
 Boss[] bosses;
}

Answer 5

This is a classic issue between databases where many to many doesn't work, hence your helper table, and the object world where many to many works fine. As soon as the relationship has attributes then you should create a new class to hold that information. However, you'll save yourself a lot of head time if you look at Object Relation Mapping - ORM - that whole field grew up to solve this (and many other) problems between DB and Object.

Answer 6

If you have a simple many-to-many linking table with foreign keys from each table in the relationship, then you would model it as you suggest: Boss has a collection of Dogs and Dog has a collection of Bosses.

If you have a many-to-many relationship with extra data, such as Nickname, then you would model that as two one-to-many relationships. Create an entity, such as DogBoss so that Boss has a collection of DogBoss and Dog has a collection of DogBoss.

Answer 7

the traditional many to many relation would have no extra fields on the matching table.

Because you do have fields with unique information I tend to stop thinking of these relations as many to many.

Once you add information to the matching table i think you have then made this table into an entity in its own right and so needs its own object to represent it.

At this point you can begin to have a DogsName class to connect a person and a dog - both of which would contain references to this object as part of a collection.

However whether you give the dog a name to be called by or own the dog are independant.

As well as modelling the relation of dogs name according to different people you also need to model the ownership relationships. In memory this would mean both objects contain a list of the other objects.

Answer 8

Something like this; It still needs some finetuning though (make the collection private and add a readonly public accessor for it which returns a readonlycollection for instance, but you'll catch the drift.

public class Dog
{
    public List<Boss> Bosses;

    public void AddBoss( Boss b )  
    {
        if( b != null && Bosses.Contains (b) == false )
        {
            Bosses.Add (b);
            b.AddDog (this);
        }
    }

    public void RemoveBoss( Boss b )
    {
         if( b !=null && Bosses.Contains (b) )
         {
             Bosses.Remove (b);
             b.RemoveDog (this);
         }
    }
}

public class Boss
{
    public List<Dog> Dogs;

    public void AddDog( Dog d )
    {
         if( d != null && Dogs.Contains (d) == false )
         {
              Dogs.Add(d);
              d.AddBoss(this);
         }
    }

    public void RemoveDog( Dog d )
    {
        if( d != null && Dogs.Contains(d) )
        {
            Dogs.Remove (d);
            d.RemoveBoss(this);
        }
    }
}

In this way, you could model a many-to-many in your code where every Dog knows his Bosses, and every Boss knows his Dogs. When you need extra data in the helper table, you'll need to create another class as well.

Answer 9

In a relational model, the best way to model a many to many relationship (using your example of Dogs/Bosses) is to have three separate tables.

One table for DOGS, one table for BOSSES (and each of these tables has a unique key), and the third table is usually a "junction table".

This table will usually have at least two fields, one field for the foreign key for a Dog and the other field for the foreign key of a Boss. This way each Dog can have many bosses, and each Boss can have many Dogs.

Now, when it come to modeling this in code in a more object-oriented manner, this is usually achieved by having a Dog class and a Boss class. As well as having the usual atomic properties for each of these objects, each one would also expose a property that is a collection of the other.

So, for example, a Dog object would have a property called "Bosses". This property would expose a collection of Boss objects that are allocated to the specific Dog object (as defined in the junction table), and on the other side, each Boss object would expose a property called Dogs which would be a collection of Dog objects allocated to that specific Boss object (as defined by the junction table).

Note that there may well be some "overlap" in these objects (i.e. one "dog" object may have "boss" objects that another "dog" object has), however, this is the traditional mechanism for translating a three-table many-to-many relational model into an object oriented one.

Answer 10

public class Boss
{
   private string name;
   private List<Hashtable> dogs;
   private int limit;

   public Boss(string name, int dogLimit)
   {
      this.name = name;
      this.dogs = new List<Hashtable>();
      this.limit = dogLimit; 
   }

   public string Name { get { return this.name; } }

   public void AddDog(string nickname, Dog dog)
   {
      if (!this.dogs.Contains(nickname) && !this.dogs.Count == limit)
      {
         this.dogs.Add(nickname, dog);
         dog.AddBoss(this);
      } 
   }

   public void RemoveDog(string nickname)
   {
       this.dogs.Remove(nickname);
       dog.RemoveBoss(this);
   }

   public void Hashtable Dogs { get { return this.dogs; } }
}

public class Dog
{
   private string name;
   private List<Boss> bosses;

   public Dog(string name)
   {
      this.name = name;
      this.bosses = new List<Boss>();
   }

   public string Name { get { return this.name; } }

   public void AddBoss(Boss boss)
   {
      if (!this.bosses.Contains(boss))
      {
          this.bosses.Add(boss);
      }
   }

   public void RemoveBoss(Boss boss)
   {
      this.bosses.Remove(boss);
   }  

   public ReadOnlyCollection<Boss> Bosses { get { return new ReadOnlyCollection<Boss>(this.bosses); } }
}

The above maintains the relationship of Bosses can have multiple dogs (with a limit applied) and dogs having multiple bosses. It also means that when a boss is adding a dog, they can specify a nickname for the dog which is unique to that boss only. Which means other bosses can add the same dog, but with different nicknames.

As for the limit, I would probably have this as an App.Config value which you just read in before instantiating the boss object(s). So a small example would be:

var boss1 = new Boss("James", ConfigurationManager.AppSettings["DogsPerBoss"]);
var boss2 = new Boss("Joe", ConfigurationManager.AppSettings["DogsPerBoss"]);

var dog = new Dog("Benji");
var dog2 = new Dog("Pooch");

boss1.AddDog("Good boy", dog);
boss2.AddDog("doggie", dog);

boss1.AddDog("rover", dog2);
boss2.AddDog("buddie", dog2);  // won't add as the preset limit has been reached.

You can obviously tweak this as you see fit, however, I think the fundamentals of what you are looking for are there.

• Boss can have multiple dogs with limit
• Dogs can have multiple bosses
• Bosses can have different nicknames for same dog.

Answer 11

Am I missing something or is the only code you need for this as follows:

List<Bosses> BossList;

class Dog {}
class Boss { Dog[] Dogs; }

You don't need to explicitly model the two-way relationship. It's implicit in the code structure. There may be other reasons to do so, but in general it is sufficient to have a one-way reference and a way to traverse the set of referencing objects.

Answer 12

Hello All, Every time we need to think about real life and our needs. In this case, the key point is which one should have another one.

In real life a dog and a boss may dont have each other. But your software needs should effect this relationship.

• For example if you are developing a veterinarian patient management software, for a veterinarian who cures stray dogs then Patient(dog)-Guardian(boss) relationship should be like this : Bosses must have at least one dog and dog may dont have any boss(then boss id is the foreign key of this relationship) that means in your design the dog class must hold a collection of bosses. Why because any boss instance can not be created without any dog(s). We can get this decision with the the data logic too. Lets think about when you are trying to save your dog and boss classes in database. If the relation condition like above, while saving a boss you should insert a junction record into the junction table.

• If you are developing that software a veterinarian who dont cures stray dogs, then Patient - Parent relationship needs to be like this: A dog must have at least one boss and boss must have at least a dog, and we need to consider this special relationship case. That means any of these classes instances can not be created without eachother. So we need to define this speciality in our OO design. That means we need a class which represents this dependency. Ofcourse this dependency will stored in junction table.

-If your software developed for a veterinarian who cures stray dogs and these dogs adopted by bosses your design should be like this: Any dog may dont have boss(es) and any boss may dont have any dog(s) until adoption. In this case our OO design needs to care about this special case. This case a little bit same as the first one. So we can add collection of any class into other one. But, any software needs like this will effect other needs. Like reporting. If veterinarian concerns about dogs which adopted by boss(es), sooner or later he asks a report which dog adopted by who. Like in the sentence, (dogs adopted by boss(es)) it is better if dog class contains a collection of boss classes.

I hope I can give proper answers to your question.

Answer 13

// Do not model classes , but model the metadata :
// Download the full project from [here][1] 

    //START ===== File.cs
    namespace Cas.IO
    {
            using System.IO;

            /// <summary>
            /// Summary for the Files class
            /// <source>Subsonic 2.1 final</source>
            /// </summary>
            public static class FileUtility
            {
                    /// <summary>
                    /// Read a text file and obtain it's contents.
                    /// </summary>
                    /// <param name="absolutePath">The complete file path to write to.</param>
                    /// <returns>String containing the content of the file.</returns>
                    public static string GetFileText(string absolutePath)
                    {
                            using (StreamReader sr = new StreamReader(absolutePath))
                                    return sr.ReadToEnd();
                    }

                    /// <summary>
                    /// Creates or opens a file for writing and writes text to it.
                    /// </summary>
                    /// <param name="absolutePath">The complete file path to write to.</param>
                    /// <param name="fileText">A String containing text to be written to the file.</param>
                    public static void CreateToFile(string absolutePath, string fileText)
                    {
                            using (StreamWriter sw = File.CreateText(absolutePath))
                                    sw.Write(fileText);
                    }

                    /// <summary>
                    /// Update text within a file by replacing a substring within the file.
                    /// </summary>
                    /// <param name="absolutePath">The complete file path to write to.</param>
                    /// <param name="lookFor">A String to be replaced.</param>
                    /// <param name="replaceWith">A String to replace all occurrences of lookFor.</param>
                    public static void UpdateFileText(string absolutePath, string lookFor, string replaceWith)
                    {
                            string newText = GetFileText(absolutePath).Replace(lookFor, replaceWith);
                            WriteToFile(absolutePath, newText);
                    }

                    /// <summary>
                    /// Writes out a string to a file.
                    /// </summary>
                    /// <param name="absolutePath">The complete file path to write to.</param>
                    /// <para0m name="fileText">A String containing text to be written to the file.</param>
                    public static void WriteToFile(string absolutePath, string fileText)
                    {
                            using (StreamWriter sw = new StreamWriter(absolutePath, false))
                                    sw.Write(fileText);
                    }

            } //eof clas 
    } //eof namespace 
    //END ================== File.cs

    . 
    //START ===== MetaItem.cs
    using System;
    using System.Collections.Generic;
    using System.Text;

    namespace MetaItem
    {
            class MetaItem
            {
                    #region Props 

                    public long MetaItemId { get; set; }
                    public long ItemId { get; set; }
                    public string Name { get; set; } 
                    public List <MetaItem> ListMasters { get ; set ; } 
                    public List <MetaItem> ListDetails { get ; set ; }
                    public string RelashionchipToMaster { get; set; }
                    public string RelashionchipToDetail { get; set; } 

                    #endregion Props 

                    #region Methods 

                    public string GenerateLinkSuffixToMaster ( MetaItem objMetaItem ) 
                    {
                            string strLink = String.Empty;
                            if (objMetaItem == null)
                                    return null ;  

                            if ( String.IsNullOrEmpty ( objMetaItem.Name ))
                                    return null ;

                            if (objMetaItem.ItemId == null)
                                    return null; 

                            strLink = "m=" + objMetaItem.Name + "&id=" + objMetaItem.ItemId.ToString();
                            return strLink; 
                    } //eof method 


                    public string GenerateLinkSuffixToDetail(MetaItem objMetaItem)
                    {
                            string strLink = String.Empty;
                            if (objMetaItem == null)
                                    return null;

                            if (String.IsNullOrEmpty(objMetaItem.Name))
                                    return null;

                            if (objMetaItem.ItemId == null)
                                    return null;

                            strLink = "d=" + objMetaItem.Name + this.ItemId.ToString() + "id=" + objMetaItem.ItemId.ToString();
                            return strLink;
                    } //eof method 


                    public string PrintLinksToMasters()
                    {
                            string strToPrint = String.Empty; 
                            foreach (MetaItem item in this.ListMasters)
                            {
                                    strToPrint = strToPrint + "The " + this.Name + this.ItemId.ToString() + 
                                            this.RelashionchipToMaster + item.Name + item.ItemId.ToString() + "\n"; 
                                    strToPrint = strToPrint + "From " + this.Name + this.ItemId.ToString() + " to " + 
                                            item.Name + item.ItemId.ToString() + "the link is :" + item.GenerateLinkSuffixToMaster(item) + "\n"; 
                            } //eof foreach

                            return strToPrint; 
                    } //eof method 


                    public string PrintLinksToDetails()
                    {
                            string strToPrint = String.Empty; 
                            foreach (MetaItem item in this.ListDetails)
                            {
                                    strToPrint = strToPrint + "The " + this.Name + this.ItemId.ToString() +
                                            this.RelashionchipToDetail + item.Name + item.ItemId.ToString() + "\n" ;
                                    strToPrint = strToPrint + "From " + this.Name + this.ItemId.ToString () + " to " +
                                             item.Name + item.ItemId.ToString() + " the link is: "  + item.GenerateLinkSuffixToDetail(item) + "\n" ; 
                            } //eof foreach

                            return strToPrint; 
                    } //eof method 

                    #endregion Melthods 



            } //eof class 
    }

    //END ================== MetaItem.cs

    . 
    //START ===== Program.cs
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace MetaItem
    {
            class Program
            {

                    static void Main(string[] args)
                    {
                            int RollingFirstLevelId = 0; //start counting the second level 
                            int intRollingSecondLevelId = 0; //start counting the second level 
                            int intRollingThirdLeveId = 0; //start counting the third level
                            int intCurrentFirstLevel = 0;
                            string strToPrint = String.Empty ; 

                            for (int intFirstLeveId = RollingFirstLevelId; intFirstLeveId <= intCurrentFirstLevel + 2; intFirstLeveId++)
                            {
                                    MetaItem objMetaItem1 = new MetaItem();
                                    objMetaItem1.Name = "Customer";
                                    objMetaItem1.ItemId = intFirstLeveId ;
                                    objMetaItem1.RelashionchipToMaster = " belongs to ";
                                    objMetaItem1.RelashionchipToDetail = " has a ";
                                    List<MetaItem> listFirstLevelMasters = new List<MetaItem>();
                                    listFirstLevelMasters.Add(objMetaItem1);
                                    List<MetaItem> listFirstLevelDetails = new List<MetaItem>();


                                    int intCurrentSecondLevel = intRollingSecondLevelId;
                                    for (int intSecondLeveId = intRollingSecondLevelId; intSecondLeveId <= intCurrentSecondLevel + 3; intSecondLeveId++)
                                    {
                                            MetaItem objMetaItem2 = new MetaItem();
                                            objMetaItem2.Name = "Order";
                                            objMetaItem2.ItemId = intSecondLeveId;
                                            objMetaItem2.RelashionchipToMaster = " belongs to ";
                                            objMetaItem2.RelashionchipToDetail = " has a ";
                                            objMetaItem2.ListMasters = listFirstLevelMasters;
                                            strToPrint = strToPrint + objMetaItem2.PrintLinksToMasters();

                                            List<MetaItem> listSecondLevelMasters = new List<MetaItem>();
                                            listSecondLevelMasters.Add(objMetaItem2);
                                            List<MetaItem> listSecondLevelDetails = new List<MetaItem>();

                                            int intCurrentThirdLevel = intRollingThirdLeveId;
                                            for (int intThirdLeveId = intRollingThirdLeveId; intThirdLeveId <= intCurrentThirdLevel + 4; intThirdLeveId++)
                                            {
                                                    MetaItem objMetaItem3 = new MetaItem();
                                                    objMetaItem3.Name = "Invoice";
                                                    objMetaItem3.ItemId = intThirdLeveId;

                                                    objMetaItem3.RelashionchipToMaster = " belongs to ";
                                                    objMetaItem3.RelashionchipToDetail = " has a ";

                                                    objMetaItem3.ListMasters = listSecondLevelMasters;
                                                    strToPrint = strToPrint + objMetaItem3.PrintLinksToMasters();
                                                    intRollingThirdLeveId++;


                                                    listSecondLevelDetails.Add(objMetaItem3);
                                                    objMetaItem2.ListDetails = listSecondLevelDetails; 
                                                    strToPrint = strToPrint + objMetaItem2.PrintLinksToDetails();



                                            }//eof for 3 
                                            intRollingSecondLevelId++;

                                            //now add 

                                            listFirstLevelDetails.Add(objMetaItem2);
                                            objMetaItem1.ListDetails = listFirstLevelDetails; 
                                            strToPrint = strToPrint + objMetaItem1.PrintLinksToDetails();
                                    }//eof for 2

                            }//eof for 1          
                            Console.WriteLine(strToPrint ); 
                            Console.WriteLine("Hit a key to exit " );
                            Console.ReadKey();

                            Cas.IO.FileUtility.WriteToFile( "C:\\temp\\file.txt" ,strToPrint );

                    } //eof Main 
            } //eof class 
    } //eof namesapce 

    //END ================== Program.cs