MySQL DB Design Questions

Question

I have some fundamental DB design questions, and I wanted to get them cleared up before I go through and draw out all of the DB relations.

Question 1

I have two tables, where the first will contain 1 instance of the second. I'll be using entity framework, and would like to be access table1 from table2, and table2 from table1.

My idea was to have a foreign key from table1 to table2, and another from table2 to table1. Adding the link from table1 to table2 seems to work. However, when I attempt to add the second link (from table2 to table1), MySQL Workbench adds two links instead of one. I'm thinking I'm doing something wrong since it's my understanding it should only add one link. Is my design wrong, or should I just delete the second link that is being added?

Question 2

Next, I'm trying to implement a join table. An instance of table1 can have many instances of table2 and vice-versa, so the join table seems to be the necessary structure to accomplish this. MySQL allows me to create either an Identifying Relationship or a Non-Identifying Relationship, and I'm unsure of which to use. Any ideas?

If more clarification is needed, please let me know.

Answer 1

As pointed out by ypercube and JesseB I think all you need is a 1..n relationship.

With Entity Framework 4.1 (and POCO Code First) all you need is a map that declares this relationship, like

this.HasRequired(t => t.Image)
  .WithMany(t => t.Words)
  .HasForeignKey(d => d.ImageId);

Please, find here a complete and working code. If launched it will create a database with all the needed foreign keys. You will see that tho sole foreign key you'd need is image_id in words table. Entity Framework is able to inject a collection of Words into any Image object, without relying on any additional foreign key.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WordAndImages.Entities;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration;

namespace WordAndImages
{

    public class Word
    {
        public int Id { get; set; }
        public int ImageId { get; set; }
        public virtual Image Image { get; set; }
        public string Value { get; set; }
    }

    public class Image
    {
        public int Id { get; set; }
        public virtual List<Word> Words { get; set; }
        public string Value { get; set; }
        public Image()
        {
            Words = new List<Word>();
        }
    }


    public class Context : DbContext
    {
        static Context()
        {
            Database.SetInitializer<Context>(null);
        }

        public DbSet<Word> Words { get; set; }
        public DbSet<Image> Images { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new WordsMap());
        }
    }

    public class WordsMap : EntityTypeConfiguration<Word>
    {
        public WordsMap()
        {
            this.HasRequired(t => t.Image)
              .WithMany(t => t.Words)
              .HasForeignKey(d => d.ImageId);
        }
    }


    class Program
    {
        static void Main(string[] args)
        {

            #region Saving a Word with an Image

            var context = new Context();
            context.Database.Delete();
            context.Database.CreateIfNotExists();

            var word = new Word();
            word.Value = "I'm a word";

            var image = new Image();
            image.Value = "I'm an image";

            word.Image = image;

            context.Words.Add(word);
            context.SaveChanges();
            #endregion

            #region Accessing an Image from a Word and viceversa

            var context2 = new Context();

            var recovered_word = context2.Words.Where(w => w.Value == "I'm a word").FirstOrDefault();
            Console.WriteLine(string.Format("I'm the word '{0}' and my image is '{1}'", word.Value, word.Image.Value));

            var recovered_image = context2.Images.Where(w => w.Value == "I'm an image").FirstOrDefault();
            Console.WriteLine(string.Format("I'm the image '{0}' and one of my images is '{1}'", recovered_image.Value, recovered_image.Words.First().Value));

            Console.ReadLine();
            #endregion
        }
    }
}

For a many-to-many relationship, just use a map like

this.HasMany(a => a.Words)
  .WithMany(z => z.Images)
  .Map(m =>
  m.ToTable("Images_Words").MapLeftKey("Word_id").MapRightKey("Image_id"));

and modify your classes as follow

public class Word
{
    public int Id { get; set; }
    public virtual List<Image> Images { get; set; }
    public string Value { get; set; }
    public Word()
    {
        Images = new List<Image>();
    }
}

public class Image
{
    public int Id { get; set; }
    public virtual List<Word> Words { get; set; }
    public string Value { get; set; }
    public Image()
    {
        Words = new List<Word>();
    }
}

If not working with legacy databases, I like designing domain objects first, and let the ORM create tables and foreign keys.

In the case you'd prefere beginning from database, take into consideration the Extension Entity Framework Power Tools CTP1, which you can download from Extension Manager: it's able to produce POCO classes from a database.