#region Copyright and GPL
/*************************************************************************
 * dataMagus - The open source database learning software
 * Copyright (C) 2007 by all authors
 * Authors: Monika Schwitter (monsy@gmx.ch)
 *          Jürg Zgraggen (j.zgraggen@ejaz.net)
 *          HSLU (Hochschule Luzern - Technik+Architektur)
 * 
 *     This program is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     This program is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU General Public License for more details.
 * 
 *     You should have received a copy of the GNU General Public License
 *     along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *************************************************************************/
#endregion
#region Using
using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using dataMagus.Common.Logical;
using System.Drawing;
#endregion

namespace dataMagus.Common.Physical.Relational
{
	/// <summary>
	/// This class is used to convert an logical schema into a physical objectoriented one.
    /// If we have a relationship between two entities, we name them entity 1 or left entity
    /// respectively entity 2 or right entity. The same goes with an association between two classes.
    /// </summary>
    public class PhysicalRelationalSchemaConverter
    {
        #region Constants
        /// <summary>
        /// Defines the default key name for a technical key.
        /// </summary>
        public const string DefaultTechnicalKeyName = "Id";
        
        /// <summary>
        /// Defines the default name for a unique key index.
        /// </summary>
        public const string DefaultUniqueKeyIndexName = "UniqueIdx";
        #endregion

        #region Enumerations
        /// <summary>
        /// Possible answer results by user interaction for a 1:1 relationship.
        /// </summary>
        /// <remarks>Answer codes are used to store.</remarks>
        private enum OneToOneAnswers
        {
        	/// <summary>
        	/// Processing a relationship having no link table and the foreign key on the left.
        	/// </summary>
            WithoutLinkTableForeignKeyLeft = 1,

        	/// <summary>
        	/// Processing a relationship having no link table and the foreign key on the right.
        	/// </summary>
            WithoutLinkTableForeignKeyRight = 2,
            
        	/// <summary>
        	/// Processing a relationship having a link table with a technical key.
        	/// </summary>
            WithLinkTableWithTechnicalKey = 3,
            
        	/// <summary>
        	/// Processing a relationship having a link table with the natural key from the left.
        	/// </summary>
            WithLinkTableWithNaturalKeyLeft = 4,
            
        	/// <summary>
        	/// Processing a relationship having a link table with the natural key from the right.
        	/// </summary>
            WithLinkTableWithNaturalKeyRight = 5
        }

        /// <summary>
        /// Possible answer results by user interaction for a 1:n  relationship.
        /// </summary>
        /// <remarks>Answer codes are used to store.</remarks>
        private enum OneToManyAnswers
        {
        	/// <summary>
        	/// Processing a relationship having no link table.
        	/// </summary>
            WithoutLinkTable = 10,

        	/// <summary>
        	/// Processing a relationship having a link table with a technical key.
        	/// </summary>
            WithLinkTableWithTechnicalKey = 11,
            
        	/// <summary>
        	/// Processing a relationship having a link table with the natural key.
        	/// </summary>
            WithLinkTableWithNaturalKey = 12
        }

        /// <summary>
        /// Possible answer results by user interaction for a n:m relationship.
        /// </summary>
        /// <remarks>Answer codes are used to store.</remarks>
        private enum ManyToManyAnswers
        {
        	/// <summary>
        	/// Processing a relationship having a link table with a technical key.
        	/// </summary>
            WithLinkTableWithTechnicalKey = 20,

        	/// <summary>
        	/// Processing a relationship having a link table with the natural key.
        	/// </summary>
            WithLinkTableWithNaturalKey = 21
        }
        #endregion

        #region Members
        /// <summary>
        /// Current physical relational schema.
        /// </summary>
        private PhysicalRelationalSchema _currentSchema;

        /// <summary>
        /// Object used to interact with the user.
        /// </summary>
        private IUserInteraction _userInteraction;
        
        /// <summary>
        /// Holds a list of all physical ids.
        /// </summary>
        private List<string> _physicalIds = new List<string>();
        
        /// <summary>
		/// Counter to create uniqueness of physical ids.
        /// </summary>
        private static int _pyhsicalIdCounter = 0;
        #endregion

        #region Constructors / Destructors
        /// <summary>
        /// Default constructor
        /// </summary>
        public PhysicalRelationalSchemaConverter()
        {   
        }
        #endregion

        #region Properties
        /// <summary>
        /// Object used to interact with the user.
        /// </summary>
        public IUserInteraction UserInteraction
        {
            get { return this._userInteraction; }
            set { this._userInteraction = value; }
        }
        #endregion

        #region Methods
        /// <summary>
        /// Convert the logical schema into the physical relational one.
        /// </summary>
        /// <param name="schema">Physical relational schema to convert in.</param>
        public void Convert(IPhysicalSchema schema)
        {
            this._physicalIds.Clear();
            PhysicalRelationalSchemaConverter._pyhsicalIdCounter = 0;

            // We need to have a reference for user interactions
            if (null == this.UserInteraction)
            {
                throw new Exception("UserInteraction must not be null");
            }

            // Buffer the schema
            this._currentSchema = (PhysicalRelationalSchema)schema;

            // Set the init mode because we don't want to fire changed events at the moment
            this._currentSchema.BeginInit();

            // Create a physical table for each logical entity
            IEnumerator entitiesEnumerator = DataContainer.Instance.LogicalSchema.GetEntities();
            while (entitiesEnumerator.MoveNext())
            {
                LogicalEntity entity = (LogicalEntity)entitiesEnumerator.Current;
                this._currentSchema.AddTable(this.CreateTable(entity));
            }

            // Create a primary key for each logical entity which does not have one
            IEnumerator tablesEnumerator = this._currentSchema.GetTables();
            while (tablesEnumerator.MoveNext())
            {
                PhysicalRelationalTable table = (PhysicalRelationalTable)tablesEnumerator.Current;

                LogicalEntity origin = (LogicalEntity)table.Origin;
                if (origin.CountKeyAttributes > 0)
                {
                    // We have key attributes defined on the logical entity, so we take them for
                    // the primary key columns
                    IEnumerator keyAttributesEnumerator = origin.GetKeyAttributes();
                    while (keyAttributesEnumerator.MoveNext())
                    {
                        LogicalAttribute attribute = (LogicalAttribute)keyAttributesEnumerator.Current;
                        PhysicalRelationalColumn col = this.CreateColumn(table, attribute);
                        table.AddPrimaryKeyColumn(col);
                    }
                }
                else
                {
                    // Check for a is-a-relationship. In case of one, we need to take
                    // the key attributes of the root entity of a is-a-relationship-chain for
                    // the primary key of the current table
                    LogicalEntity rootIsAEntity = IsALogicalRelationship.GetRootIsAEntity(origin);
                    if (null != rootIsAEntity && rootIsAEntity.CountKeyAttributes > 0)
                    {
                        IEnumerator keyAttributeEnumerator = rootIsAEntity.GetKeyAttributes();
                        while (keyAttributeEnumerator.MoveNext())
                        { 
                            LogicalAttribute attribute = (LogicalAttribute)keyAttributeEnumerator.Current;
                            PhysicalRelationalColumn col = this.CreateColumn(table, attribute);
                            table.AddPrimaryKeyColumn(col);
                        }
                    }
                    else
                    {
                        // We don't have key attributes and we don't are a is-a-relationship, so
                        // we need to create a technical key column on our own
                        PhysicalRelationalColumn col = this.CreateTechnicalKeyColumn(table);
                        table.AddPrimaryKeyColumn(col);
                    }
                }
            }

            // Loop through the logical relationships and transfer them to physical ones
            IEnumerator relEnumerator = DataContainer.Instance.LogicalSchema.GetRelationships();
            while (relEnumerator.MoveNext())
            { 
                LogicalRelationship rel = (LogicalRelationship)relEnumerator.Current;
            
                PhysicalRelationalTable table1 = this.FindTableForLogicalEntity(rel.Entity1);
                PhysicalRelationalTable table2 = this.FindTableForLogicalEntity(rel.Entity2);

                if (rel is IsALogicalRelationship)
                {
                    this.AddIsARelationship((IsALogicalRelationship)rel, table1, table2);
                }
                else if (rel is OneToManyLogicalRelationship)
                {
                    this.AddOneToManyRelationship((OneToManyLogicalRelationship)rel, table1, table2);
                }
                else if (rel is OneToOneLogicalRelationship)
                {
                    this.AddOneToOneRelationship((OneToOneLogicalRelationship)rel, table1, table2);
                }
                else if (rel is ManyToManyLogicalRelationship)
                {
                    this.AddManyToManyRelationship((ManyToManyLogicalRelationship)rel, table1, table2);
                }
            }

            // End the init mode and fire the changed event
            this._currentSchema.EndInit(true);
        }

		/// <summary>
		/// Ensure that there exists a physical id only once.
		/// </summary>
		/// <param name="physicalId">Physical id to check.</param>
		/// <returns>New physical id.</returns>
        private string EnsureUniquePhysicalId(string physicalId)
        {
            string id = physicalId;
            if (this._physicalIds.Contains(physicalId))
            {
                id  = String.Format("{0}#{1}", id, ++PhysicalRelationalSchemaConverter._pyhsicalIdCounter);
                
            	// inform tracer about the problem, but don't let the converter burst
            	Trace.TraceError(string.Format("Error: The generated physical ID ({0}) is not unique! A new one ({1}) has been created:", physicalId, id));
            }
            this._physicalIds.Add(id);
            return id;
        }

        /// <summary>
        /// Create a physical id for a link table.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <returns>New physical id.</returns>
        private string CreateLinkTablePhysicalId(LogicalRelationship relationship)
        {
            string id = String.Format("{0}", relationship.Id);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for an index.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="table">Table.</param>
        /// <returns>New physical id.</returns>
        private string CreateIndexPhysicalId(PhysicalRelationalRelationship relationship, PhysicalRelationalTable table)
        {
            string id = String.Format("{0}_{1}_{2}", relationship.Id, table.Id, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for an index.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="tableLeft">Left table</param>
        /// <param name="tableRight">Right table</param>
        /// <returns>New physical id.</returns>
        private string CreateIndexPhysicalId(PhysicalRelationalRelationship relationship, PhysicalRelationalTable tableLeft, PhysicalRelationalTable tableRight)
        {
            string id = String.Format("{0}_{1}_{2}_{3}", relationship.Id, tableLeft.Id, tableRight.Id, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a relationship.
        /// </summary>
        /// <param name="entityLeft">Left entity</param>
        /// <param name="tableRight">Right table</param>
        /// <param name="rel">Relationship</param>
        /// <returns>New physical id.</returns>
        private string CreateRelationshipPhysicalId(LogicalEntity entityLeft, PhysicalRelationalTable tableRight, LogicalRelationship rel)
        {
            string id = String.Format("{0}_{1}_{2}", entityLeft.Id, tableRight.Id, rel.Id);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a relationship.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <returns>New physical id.</returns>
        private string CreateRelationshipPhysicalId(LogicalRelationship relationship)
        {
            string id = relationship.Id;
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a relationship.
        /// </summary>
        /// <param name="tableLeft"></param>
        /// <param name="entityRight"></param>
        /// <param name="rel">Relationship</param>
        /// <returns>New physical id.</returns>
        private string CreateRelationshipPhysicalId(PhysicalRelationalTable tableLeft, LogicalEntity entityRight, LogicalRelationship rel)
        {
            string id = String.Format("{0}_{1}_{2}", tableLeft.Id, entityRight.Id, rel.Id);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a technical key.
        /// </summary>
        /// <param name="table"></param>
        /// <returns>New physical id.</returns>
        private string CreateTechnicalKeyPhysicalId(PhysicalRelationalTable table)
        {
            string id = String.Format("{0}_{1}", table.Id, PhysicalRelationalSchemaConverter.DefaultTechnicalKeyName);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a foreign key from the left.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="pkColumn">Primary key column.</param>
        /// <param name="pkTable">Table containing the primary key column.</param>
        /// <param name="fkTable">Table the foreign key will be in.</param>
        /// <returns>New physical id.</returns>
        private string CreateForeignKeyFromLeftPhysicalId(LogicalRelationship relationship,
            PhysicalRelationalColumn pkColumn, PhysicalRelationalTable pkTable, PhysicalRelationalTable fkTable)
        {
            string id = String.Format("{0}_{1}_{2}_{3}", pkColumn.Id, fkTable.Id, pkTable.Id, relationship.Id);
            return this.EnsureUniquePhysicalId(id);
        }

        /// <summary>
        /// Create a physical id for a foreign key from the right.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="pkColumn">Primary key column.</param>
        /// <param name="pkTable">Table containing the primary key column.</param>
        /// <param name="fkTable">Table the foreign key will be in.</param>
        /// <returns>New physical id.</returns>
        private string CreateForeignKeyFromRightPhysicalId(LogicalRelationship relationship,
            PhysicalRelationalColumn pkColumn, PhysicalRelationalTable pkTable, PhysicalRelationalTable fkTable)
        {
            string id = String.Format("{0}_{1}_{2}_{3}", pkColumn.Id, pkTable.Id, fkTable.Id, relationship.Id);
            return this.EnsureUniquePhysicalId(id);
        }
        
        /// <summary>
        /// Find the physical table which was generated for the logical entity.
        /// </summary>
        /// <param name="entity">Entity to search for.</param>
        /// <returns>The table or null if nothing has been foudn.</returns>
        private PhysicalRelationalTable FindTableForLogicalEntity(LogicalEntity entity)
        {
            IEnumerator tablesEnumerator = this._currentSchema.GetTables();
            while (tablesEnumerator.MoveNext())
            {
                PhysicalRelationalTable table = (PhysicalRelationalTable)tablesEnumerator.Current;
                if (table.Id == entity.Id)
                {
                    return table;
                }
            }
            return null;
        }

        /// <summary>
        /// Create a physical column for a logical attribute.
        /// </summary>
        /// <param name="parent">Parent table the column will be in.</param>
        /// <param name="attribute">Attribute the column is based on.</param>
        /// <returns>New column.</returns>
        private PhysicalRelationalColumn CreateColumn(PhysicalRelationalTable parent, LogicalAttribute attribute)
        {
            return new PhysicalRelationalColumn(this._currentSchema, parent, attribute.Id, 
                attribute, Helper.ToTitleCase(attribute.Name), !attribute.IsPartOfKey);
        }

        /// <summary>
        /// Create a physical technical key column.
        /// </summary>
        /// <param name="parent">Parent table the key will be in.</param>
        /// <returns>New column.</returns>
        private PhysicalRelationalColumn CreateTechnicalKeyColumn(PhysicalRelationalTable parent)
        {
            return new PhysicalRelationalColumn(this._currentSchema, parent,
                this.CreateTechnicalKeyPhysicalId(parent), null, 
                PhysicalRelationalSchemaConverter.DefaultTechnicalKeyName, false);
        }

        /// <summary>
        /// Create a physical unique index.
        /// </summary>
        /// <param name="id">Id of the index.</param>
        /// <param name="name">Name of the index.</param>
        /// <returns>New index.</returns>
        private PhysicalRelationalIndex CreateUniqueIndex(string id, string name)
        {
            return new PhysicalRelationalIndex(
                this._currentSchema, id, null, Helper.ToTitleCase(name),
                PhysicalRelationalIndex.IndexType.Unique, true);
        }

        /// <summary>
        /// Create a physical table for a logical entity.
        /// </summary>
        /// <param name="entity">Entity the table is based on.</param>
        /// <returns>New table.</returns>
        private PhysicalRelationalTable CreateTable(LogicalEntity entity)
        {
            PhysicalRelationalTable table = new PhysicalRelationalTable(this._currentSchema,
                entity.Id, entity, Helper.ToTitleCase(entity.Name));
        	if (table.Location == Point.Empty)
        	{
        		table.Location = new Point(entity.Location.X, entity.Location.Y);
        	}
        	
            IEnumerator attributeEnumerator = entity.GetAttributes();
            while (attributeEnumerator.MoveNext())
            {
                LogicalAttribute attribute = (LogicalAttribute)attributeEnumerator.Current;
                if (!entity.ContainsKeyAttribute(attribute))
                {
                    table.AddColumn(this.CreateColumn(table, attribute));
                }
            }
            return table;
        }

        /// <summary>
        /// Create a physical link table for a logical relationship.
        /// </summary>
        /// <param name="relationship">Relationship the table is based on.</param>
        /// <returns>New table</returns>
        private PhysicalRelationalTable CreateLinkTable(LogicalRelationship relationship)
        {
            PhysicalRelationalTable linkTable = new PhysicalRelationalTable(this._currentSchema,
                this.CreateLinkTablePhysicalId(relationship),
                relationship, Helper.ToTitleCase(relationship.Name));
        	if (linkTable.Location == Point.Empty)
        	{
        		linkTable.Location = new Point(relationship.Location.X, relationship.Location.Y);
        	}
        	
            if (relationship is IAttributable)
            {
                IEnumerator attributesEnumerator = ((IAttributable)relationship).GetAttributes();
                List<LogicalAttribute> keyAttributes = new List<LogicalAttribute>();
                if (relationship is ManyToManyLogicalRelationship)
                {
                    IEnumerator keyAttributesEnumerator = ((ManyToManyLogicalRelationship)relationship).GetKeyAttributes();
                    while (keyAttributesEnumerator.MoveNext())
                    {
                        LogicalAttribute attribute = (LogicalAttribute)keyAttributesEnumerator.Current;
                        keyAttributes.Add(attribute);
                    }
                }
                while (attributesEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)attributesEnumerator.Current;
                    PhysicalRelationalColumn column = this.CreateColumn(linkTable, attribute);
                    // only add if the attribute is not a key attribute
                    if (0 == keyAttributes.Count || !keyAttributes.Contains(attribute))
                    {
                        linkTable.AddColumn(column);
                    }
                }
            }
            return linkTable;
        }

        /// <summary>
        /// Create physical tables and relationships out of a logical is-a-relationship between two physical tables.
        /// </summary>
        /// <param name="rel">Logical relationship the new physical one is based on.</param>
        /// <param name="table1">Table 1 of the new relationship.</param>
        /// <param name="table2">Table 2 of the new relationship.</param>
        private void AddIsARelationship(IsALogicalRelationship rel,
            PhysicalRelationalTable table1, PhysicalRelationalTable table2)
        {
            OneToOnePhysicalRelationalRelationship oneToOneRel = new OneToOnePhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(rel), rel, 
                Helper.ToTitleCase(rel.Name), table1, table2,
                Helper.ToTitleCase(rel.Rolename1), 
                Helper.ToTitleCase(rel.Rolename2),
                OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                OneToOnePhysicalRelationalRelationship.Cardinality.ZeroOrOne);

            // at this point, we can be sure, that table1 and table2 have the same primary keys and also
            // in the same order because we checked that when we create the primary keys earlier
            IEnumerator pkColsTable1Enumerator = table1.GetPrimaryKeyColumns();
            IEnumerator pkColsTable2Enumerator = table2.GetPrimaryKeyColumns();
            // Attention: do NOT use the shortcut operator here!!!
            while (pkColsTable1Enumerator.MoveNext() & pkColsTable2Enumerator.MoveNext())
            {
                PhysicalRelationalColumn pkColTable1 = (PhysicalRelationalColumn)pkColsTable1Enumerator.Current;
                PhysicalRelationalColumn pkColTable2 = (PhysicalRelationalColumn)pkColsTable2Enumerator.Current;
                oneToOneRel.AddColumnMapping(pkColTable1, pkColTable2);
            }

            this._currentSchema.AddRelationship(oneToOneRel);
        }

        /// <summary>
        /// Create physical tables and relationships out of a logical 1:1-relationship between two physical tables.
        /// </summary>
        /// <param name="rel">Logical relationship the new physical one is based on.</param>
        /// <param name="table1">Table 1 of the new relationship.</param>
        /// <param name="table2">Table 2 of the new relationship.</param>
        private void AddOneToOneRelationship(OneToOneLogicalRelationship rel,
            PhysicalRelationalTable table1, PhysicalRelationalTable table2)
        { 
            // Do we already have a valid answer?
            object savedAnswer = DataContainer.Instance.PhysicalData.GetInteractionDataItem(
                this._currentSchema, rel.Id);

            string title = String.Format("{0} {1}", global::dataMagus.Common.OtherResources.PhysicalRelationalRelationship, rel.Name);
            // Set default answer to initialize
            OneToOneAnswers givenAnswer = OneToOneAnswers.WithoutLinkTableForeignKeyRight;
 
            // Get the answer
            if (null == savedAnswer)
            {
                // Question 1: Do you want to have a link table?
                string question1 = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionBeginning;
                Dictionary<int, string> allowedAnswers1 = new Dictionary<int, string>();
                allowedAnswers1[0] = String.Format(global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneForeignKeyLeft, table1.Name);
                allowedAnswers1[1] = String.Format(global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneForeignKeyRight, table2.Name);
                allowedAnswers1[2] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneLinkTable;

                int answer1 = this.UserInteraction.AskQuestion(title, question1, String.Empty, allowedAnswers1);
                if (0 == answer1)
                {
                    givenAnswer = OneToOneAnswers.WithoutLinkTableForeignKeyLeft;
                }
                else if (2 == answer1)
                {
                    // Question 2: Technical key or one of the natural key?
                    string question2 = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionBeginning;
                    Dictionary<int, string> allowedAnswers2 = new Dictionary<int, string>();
                    allowedAnswers2[0] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneTechnicalKey;
                    allowedAnswers2[1] = String.Format(global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneNaturalKeyLeft, table1.Name);
                    allowedAnswers2[2] = String.Format(global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToOneNaturalKeyRight, table2.Name);

                    int answer2 = this.UserInteraction.AskQuestion(title, question2, String.Empty, allowedAnswers2);
                    if (-1 == answer2 || 2 == answer2)
                    {
                        // Default answer
                        givenAnswer = OneToOneAnswers.WithLinkTableWithNaturalKeyRight;
                    }
                    else if (0 == answer2)
                    {
                        givenAnswer = OneToOneAnswers.WithLinkTableWithTechnicalKey;
                    }
                    else if (1 == answer2)
                    {
                        givenAnswer = OneToOneAnswers.WithLinkTableWithNaturalKeyLeft;
                    }
                }
            }
            else
            {
                givenAnswer = (OneToOneAnswers)savedAnswer;
            }

            // Save the answer
            DataContainer.Instance.PhysicalData.UpdateInteractionDataItem(
                this._currentSchema, rel.Id, givenAnswer);

            // We have a valid answer --> create physical elements for it
            if (OneToOneAnswers.WithoutLinkTableForeignKeyRight == givenAnswer)
            { 
                // Create a normal one-to-one relationship without link table but foreign key on the right

                // Create a physical one-to-one relationship and add it to the schema
                OneToOnePhysicalRelationalRelationship physicalRel = this.CreateOneToOne(rel, table1, table2);
                this._currentSchema.AddRelationship(physicalRel);

                // Create a unique index for the foreign key colums
                PhysicalRelationalIndex uniqueIndex = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(physicalRel, table2),
                    String.Format("{0}{1}", table2.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                table2.AddIndex(uniqueIndex);

                // Create a foreign key for each primary key on the table on the right,
                // add them to a unique index and add the column mapping to the relationship
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, table2);
                    table2.AddColumn(fkCol);
                    uniqueIndex.AddColumn(fkCol);
                    physicalRel.AddColumnMapping(pkCol, fkCol);
                }

                // Create fields for the attributes on the relationship and add them to the table with the foreign keys on it
                IEnumerator attributesEnumerator = rel.GetAttributes();
                while (attributesEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)attributesEnumerator.Current;
                    PhysicalRelationalColumn col = this.CreateColumn(table2, attribute);
                    table2.AddColumn(col);
                }
            }
            else if (OneToOneAnswers.WithoutLinkTableForeignKeyLeft == givenAnswer)
            { 
                // Create a normal one-to-one relationship without link table but foreign key on the left
                // --> the trick is to take to switch the tables in the relationship!!! 

                OneToOnePhysicalRelationalRelationship physicalRel = this.CreateOneToOne(rel, table2, table1);
                this._currentSchema.AddRelationship(physicalRel);

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex uniqueIndex = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(physicalRel, table1),
                    String.Format("{0}{1}", table2.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                table1.AddIndex(uniqueIndex);

                // Create a foreign key for each primary key on the table on the left,
                // add them to a unique index and add the column mapping to the relationship
                IEnumerator pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table2, table1);
                    table1.AddColumn(fkCol);
                    uniqueIndex.AddColumn(fkCol);
                    physicalRel.AddColumnMapping(pkCol, fkCol);
                }

                // Create fields for the attribute on the relationship and add them to the primary key columns
                IEnumerator attributesEnumerator = rel.GetAttributes();
                while (attributesEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)attributesEnumerator.Current;
                    PhysicalRelationalColumn col = this.CreateColumn(table1, attribute);
                    table1.AddColumn(col);
                }
            }
            else if (OneToOneAnswers.WithLinkTableWithTechnicalKey == givenAnswer)
            { 
                // Create a one-to-one relationship with a link table which has a technical key

                // Create the link table and add a primary key column on it
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                PhysicalRelationalColumn pkColLinkTable = this.CreateTechnicalKeyColumn(linkTable);

                linkTable.AddPrimaryKeyColumn(pkColLinkTable);
                linkTable.AddColumn(pkColLinkTable);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToOneLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relTable2ToLinkTable = this.CreateOneToOneRightToLinkTable(
                    rel, table2, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relTable2ToLinkTable);

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex uniqueIndexLeft = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable1ToLinkTable, linkTable, table1),
                    String.Format("{0}{1}_1", linkTable.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(uniqueIndexLeft);

                // Create a foreign key on the link table for each primary key on the table on the left,
                // add the column mapping to the link table and add the foreign key column to the unique index
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddColumn(fkCol);
                    uniqueIndexLeft.AddColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex uniqueIndexRight = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable2ToLinkTable, linkTable, table2),
                    String.Format("{0}{1}_2", linkTable.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(uniqueIndexRight);

                // Create a foreign key on the link table for each primary key on the table on the right, 
                // add the column mapping to the link table and add the foreign key column to the unique index
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddColumn(fkCol);
                    uniqueIndexRight.AddColumn(fkCol);
                    relTable2ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }
            }
            else if (OneToOneAnswers.WithLinkTableWithNaturalKeyRight == givenAnswer)
            { 
                // Create an one-to-one relationship with a link table and take the primary key columns
                // on the table on the right as natural keys

                // Create the link table
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToOneLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relTable2ToLinkTable = this.CreateOneToOneRightToLinkTable(
                    rel, table2, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relTable2ToLinkTable);

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex uniqueIndex = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable2ToLinkTable, linkTable, table2),
                    String.Format("{0}{1}", linkTable.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(uniqueIndex);

                // Create a foreign key on the link table for each primary key on the table on the left,
                // add the column mapping to the link table and add the foreign key column to the unique index
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddColumn(fkCol);
                    uniqueIndex.AddColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a foreign key on the link table for each primary key on the table on the right,
                // mark it as a primary key and add the column mapping to the link table 
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddPrimaryKeyColumn(fkCol);
                    relTable2ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }
            }
            else if (OneToOneAnswers.WithLinkTableWithNaturalKeyLeft == givenAnswer)
            { 
                // Create a one-to-one relationship with a link table and take the primary key columns
                // on the table on the left as natural keys

                // Create the link table
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToOneLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relTable2ToLinkTable = this.CreateOneToOneRightToLinkTable(
                    rel, table2, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToOneCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relTable2ToLinkTable);

                // Create a foreign key on the link table for each primary key on the table on the left,
                // mark it as a primary key and add the column mapping to the link table
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddPrimaryKeyColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex uniqueIndex = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable1ToLinkTable, linkTable, table1),
                    String.Format("{0}{1}", table2.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(uniqueIndex);

                // Create a foreign key on the link table for each primary key on the table on the right,
                // add the column mapping to the link table and add the foreign key column to the unique index
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddColumn(fkCol);
                    uniqueIndex.AddColumn(fkCol);
                    relTable2ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }
            }
            else
            {
                throw new NotImplementedException("Found unhandled one-to-one relationship answer.");
            }
        }

        /// <summary>
        /// Create physical tables and relationships out of a logical 1:n relationship between two phyiscal tables.
        /// </summary>
        /// <param name="rel">Logical relationship the new physical one is based on.</param>
        /// <param name="table1">Table 1 of the new relationship.</param>
        /// <param name="table2">Table 2 of the new relationship.</param>
        private void AddOneToManyRelationship(OneToManyLogicalRelationship rel,
            PhysicalRelationalTable table1, PhysicalRelationalTable table2)
        {
            // Do we already have a valid answer?
            object savedAnswer = DataContainer.Instance.PhysicalData.GetInteractionDataItem(
                this._currentSchema, rel.Id);

            string title = String.Format("{0} {1}", global::dataMagus.Common.OtherResources.PhysicalRelationalRelationship, rel.Name);
            // Set default answer to initialize
            OneToManyAnswers givenAnswer = OneToManyAnswers.WithoutLinkTable;

            // Get the answer
            if (null == savedAnswer)
            {
                // Question 1: Do you want to have a link table?
                string question1 = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToManyLinkTable;
                Dictionary<int, string> allowedAnwers1 = new Dictionary<int, string>();
                allowedAnwers1[0] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToManyLinkTableYes;
                allowedAnwers1[1] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToManyLinkTableNo;
                int answer1 = this.UserInteraction.AskQuestion(title, question1, String.Empty, allowedAnwers1);
                if (0 == answer1)
                {
                    // Question 2: Technical key or natural key?
                    string question2 = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionBeginning;
                    Dictionary<int, string> allowedAnwers2 = new Dictionary<int, string>();
                    allowedAnwers2[0] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToManyTechnicalKey;
                    allowedAnwers2[1] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionOneToManyNaturalKey;

                    int answer2 = this.UserInteraction.AskQuestion(title, question2, String.Empty, allowedAnwers2);
                    if (-1 == answer2 || 0 == answer2)
                    {
                        // Default answer
                        givenAnswer = OneToManyAnswers.WithLinkTableWithTechnicalKey;
                    }
                    else if (1 == answer2)
                    {
                        givenAnswer = OneToManyAnswers.WithLinkTableWithNaturalKey;
                    }
                }
            }
            else
            {
                givenAnswer = (OneToManyAnswers)savedAnswer;
            }

            // Save the answer
            DataContainer.Instance.PhysicalData.UpdateInteractionDataItem(
                this._currentSchema, rel.Id, givenAnswer);

            // We have a valid answer --> create physical elements for it
            if (OneToManyAnswers.WithoutLinkTable == givenAnswer)
            {
                // Create a normal one-to-many relationship without link table

                // Create a physical one-to-many relationship and add it to the schema
                OneToManyPhysicalRelationalRelationship physicalRel = new OneToManyPhysicalRelationalRelationship(
                    this._currentSchema, rel.Id, rel, 
                    Helper.ToTitleCase(rel.Name),
                    table1, table2, 
                    Helper.ToTitleCase(rel.Rolename1), 
                    Helper.ToTitleCase(rel.Rolename2),
                    this.MapOneToManyCardinalityForOne(rel.Cardinality1),
                    this.MapOneToManyCardinalityForMany(rel.Cardinality2));

                this._currentSchema.AddRelationship(physicalRel);

                // Create foreign keys and add those to the table on the right side.
                // Add the column mapping for the primarykey and foreign key on the relationship
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = new PhysicalRelationalColumn(
                        this._currentSchema,
                        table2,
                        this.CreateForeignKeyFromLeftPhysicalId(rel, pkCol, table1, table2),
                        pkCol,
                        String.Format("{0}{1}", Helper.ToTitleCase(rel.Rolename1), pkCol.Origin == null ? PhysicalRelationalSchemaConverter.DefaultTechnicalKeyName : Helper.ToTitleCase(pkCol.Origin.Name)),
                        pkCol.DataType);

                    table2.AddColumn(fkCol);
                    physicalRel.AddColumnMapping(pkCol, fkCol);
                }

                // Create fields for the attributes on the relationship and add them to the table on the right
                IEnumerator attributesEnumerator = rel.GetAttributes();
                while (attributesEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)attributesEnumerator.Current;
                    PhysicalRelationalColumn col = this.CreateColumn(table2, attribute);
                    table2.AddColumn(col);
                }
            }
            else if (OneToManyAnswers.WithLinkTableWithTechnicalKey == givenAnswer)
            {
                // Create a one-to-many-relationship with a link table which has a technical key

                // Create the link table and add a primary key column on it
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                PhysicalRelationalColumn pkColLinkTable = this.CreateTechnicalKeyColumn(linkTable);

                linkTable.AddPrimaryKeyColumn(pkColLinkTable);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToManyLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapOneToManyCardinalityForMany(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relLinkTableToTable2 = this.CreateOneToOneRightToLinkTable(
                    rel, table2, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToManyCardinalityForOneForOneToOneCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relLinkTableToTable2);

                // Create a foreign key for each primary key on the table on the left and 
                // add the column mapping to the link table
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex pkIndexLinkTable = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable1ToLinkTable, linkTable),
                    String.Format("{0}{1}", table2.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(pkIndexLinkTable);

                // Create a foreign key for each primary key on the table on the right,
                // create the column mapping and add the foreign key to the index
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddColumn(fkCol);
                    pkIndexLinkTable.AddColumn(fkCol);
                    relLinkTableToTable2.AddColumnMapping(pkCol, fkCol);
                }
            }
            else if (OneToManyAnswers.WithLinkTableWithNaturalKey == givenAnswer)
            {
                // Create a one-to-many-relationship with a link table with natural keys

                // Create the link table
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToManyLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapOneToManyCardinalityForMany(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relLinkTableToTable2 = this.CreateOneToOneRightToLinkTable(
                    rel, table2, linkTable,
                    OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne,
                    this.MapOneToManyCardinalityForOneForOneToOneCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relLinkTableToTable2);

                // Create a foreign key for each primary key on the table on the left
                // and add the column mapping to the link table
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a foreign key for each primary key on the table on the right, mark it
                // as a primary key column and create the column mapping
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddPrimaryKeyColumn(fkCol);
                    relLinkTableToTable2.AddColumnMapping(pkCol, fkCol);
                }
            }
            else
            {
                throw new NotImplementedException("Found unhandled OneToMany-relationship answer.");
            }
        }

        /// <summary>
        /// Create physical tables and relationships out of a logical 1:n relationship between two phyiscal tables.
        /// </summary>
        /// <param name="rel">Logical relationship the new physical one is based on.</param>
        /// <param name="table1">Table 1 of the new relationship.</param>
        /// <param name="table2">Table 2 of the new relationship.</param>
        private void AddManyToManyRelationship(ManyToManyLogicalRelationship rel,
            PhysicalRelationalTable table1, PhysicalRelationalTable table2)
        { 
            // Do we already have a valid answer?
            object savedAnswer = DataContainer.Instance.PhysicalData.GetInteractionDataItem(
                this._currentSchema, rel.Id);

            string title = String.Format("{0} {1}", global::dataMagus.Common.OtherResources.PhysicalRelationalRelationship, rel.Name);
            // Set default ansert to initalize
            ManyToManyAnswers givenAnswer = ManyToManyAnswers.WithLinkTableWithNaturalKey;

            // Get the answer
            if (null == savedAnswer)
            {
                // Question: Technical key or natural key?
                string question = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionBeginning;
                Dictionary<int, string> allowedAnswers = new Dictionary<int, string>();
                allowedAnswers[0] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionManyToManyTechnicalKey;
                allowedAnswers[1] = global::dataMagus.Common.OtherResources.PhysicalRelationalQuestionManyToManyNaturalKey;

                int answer = this.UserInteraction.AskQuestion(title, question, String.Empty, allowedAnswers);
                if (0 == answer)
                {
                    givenAnswer = ManyToManyAnswers.WithLinkTableWithTechnicalKey;
                }
            }
            else
            {
                givenAnswer = (ManyToManyAnswers)savedAnswer;
            }

            // Save the answer
            DataContainer.Instance.PhysicalData.UpdateInteractionDataItem(this._currentSchema, rel.Id, givenAnswer);

            // We have a valid answer --> create physical elements for it
            if (ManyToManyAnswers.WithLinkTableWithTechnicalKey == givenAnswer)
            { 
                // Create a many-to-many-relationiship with a link table and add a technical key column on the link table
                
                // Create the link table and add a primary key column on it
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                PhysicalRelationalColumn pkColLinkTable = this.CreateTechnicalKeyColumn(linkTable);

                linkTable.AddPrimaryKeyColumn(pkColLinkTable);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToManyLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapManyToManyCardinality(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relTable2ToLinkTable = this.CreateOneToManyRightToLinkTable(
                    rel, table2, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapManyToManyCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relTable2ToLinkTable);

                // Create a unique index for the foreign key columns
                PhysicalRelationalIndex pkIndexLinkTable = this.CreateUniqueIndex(
                    this.CreateIndexPhysicalId(relTable1ToLinkTable, linkTable),
                    String.Format("{0}{1}", linkTable.Name, PhysicalRelationalSchemaConverter.DefaultUniqueKeyIndexName));
                linkTable.AddIndex(pkIndexLinkTable);

                // Create a foreign key on the link table for each primary key on the table on the left,
                // add the column mapping to the link table and add the foreign key column to the unique index
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddColumn(fkCol);
                    pkIndexLinkTable.AddColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a foreign key on the link table for each primary key on the table on the right,
                // add the column mapping to the link table and add the foreign key column to the unique index
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddColumn(fkCol);
                    pkIndexLinkTable.AddColumn(fkCol);
                    relTable2ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create fields for the key attributes on the relationship and add them to the unique index
                IEnumerator keyAttribEnumerator = rel.GetKeyAttributes();
                while (keyAttribEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)keyAttribEnumerator.Current;
                    PhysicalRelationalColumn col = this.CreateColumn(linkTable, attribute);
                    linkTable.AddColumn(col);
                    pkIndexLinkTable.AddColumn(col);
                }
            }
            else if (ManyToManyAnswers.WithLinkTableWithNaturalKey == givenAnswer)
            {
                // Create a many-to-many relationship with a link table and use natural keys
                PhysicalRelationalTable linkTable = this.CreateLinkTable(rel);
                this._currentSchema.AddTable(linkTable);

                // Create the relationship between the table on the left and the link table
                PhysicalRelationalRelationship relTable1ToLinkTable = this.CreateOneToManyLeftToLinkTable(
                    rel, table1, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapManyToManyCardinality(rel.Cardinality2));

                this._currentSchema.AddRelationship(relTable1ToLinkTable);

                // Create the relationship between the link table and the table on the right
                PhysicalRelationalRelationship relTable2ToLinkTable = this.CreateOneToManyRightToLinkTable(
                    rel, table2, linkTable,
                    OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne,
                    this.MapManyToManyCardinality(rel.Cardinality1));

                this._currentSchema.AddRelationship(relTable2ToLinkTable);

                // Create a foreign key on the link table for each primary key on the table on the left,
                // add the column mapping to the link table and add the foreign key column to the primary key columns
                IEnumerator pkColsEnumerator = table1.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromLeft(rel, pkCol, table1, linkTable);
                    linkTable.AddPrimaryKeyColumn(fkCol);
                    relTable1ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create a foreign key on the link table for each primary key on the table on the left,
                // add the column mapping on the link table and add the foreign key column to the primary key columns
                pkColsEnumerator = table2.GetPrimaryKeyColumns();
                while (pkColsEnumerator.MoveNext())
                {
                    PhysicalRelationalColumn pkCol = (PhysicalRelationalColumn)pkColsEnumerator.Current;

                    PhysicalRelationalColumn fkCol = this.CreateForeignKeyFromRight(rel, pkCol, table2, linkTable);
                    linkTable.AddPrimaryKeyColumn(fkCol);
                    relTable2ToLinkTable.AddColumnMapping(pkCol, fkCol);
                }

                // Create fields for the key attributes on the relationship and add them to the primary key columns
                IEnumerator keyAttribEnumerator = rel.GetKeyAttributes();
                while (keyAttribEnumerator.MoveNext())
                {
                    LogicalAttribute attribute = (LogicalAttribute)keyAttribEnumerator.Current;
                    PhysicalRelationalColumn col = this.CreateColumn(linkTable, attribute);
                    linkTable.AddPrimaryKeyColumn(col);
                }
            }
            else
            {
                throw new NotImplementedException("Found unhandled many-to-many relationship answer.");
            }
        }

        /// <summary>
        /// Create a foreign key column for a primary key column which is in the table on the left
        /// side of a logical relationship.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="pkColumn">Primary key column.</param>
        /// <param name="pkTable">Table containing the primary key column.</param>
        /// <param name="fkTable">Table the foreign key will be in.</param>
        /// <returns>New column.</returns>
        private PhysicalRelationalColumn CreateForeignKeyFromLeft(LogicalRelationship relationship,
            PhysicalRelationalColumn pkColumn, PhysicalRelationalTable pkTable, 
            PhysicalRelationalTable fkTable)
        {
            return new PhysicalRelationalColumn(
                this._currentSchema,
                fkTable,
                this.CreateForeignKeyFromLeftPhysicalId(relationship, pkColumn, pkTable, fkTable),
                pkColumn,
                String.Format("{0}{1}", 
                    String.IsNullOrEmpty(relationship.Rolename1) ? pkTable.Name : Helper.ToTitleCase(relationship.Rolename1),
                    pkColumn.Origin == null ? PhysicalRelationalSchemaConverter.DefaultTechnicalKeyName : Helper.ToTitleCase(pkColumn.Origin.Name)),
                pkColumn.DataType);
        }

        /// <summary>
        /// Create a foreign key for a primary key column which is in the table on the right 
        /// side of a logical relationship.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="pkColumn">Primary key column.</param>
        /// <param name="pkTable">Table containing the primary key column.</param>
        /// <param name="fkTable">Table the foreign key will be in.</param>
        /// <returns>New column.</returns>
        private PhysicalRelationalColumn CreateForeignKeyFromRight(LogicalRelationship relationship,
            PhysicalRelationalColumn pkColumn, PhysicalRelationalTable pkTable,
            PhysicalRelationalTable fkTable)
        {
            return new PhysicalRelationalColumn(
                this._currentSchema,
                fkTable,
                this.CreateForeignKeyFromRightPhysicalId(relationship, pkColumn, pkTable, fkTable),
                pkColumn,
                String.Format("{0}{1}", 
                    String.IsNullOrEmpty(relationship.Rolename2) ? pkTable.Name : Helper.ToTitleCase(relationship.Rolename2), 
                    pkColumn.Origin == null ? PhysicalRelationalSchemaConverter.DefaultTechnicalKeyName : Helper.ToTitleCase(pkColumn.Origin.Name)),
                pkColumn.DataType);
        }

        /// <summary>
        /// Create a physical one-to-many relationship out of a logical one-to-many relationship
        /// between a table on the left and a link table.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="tableLeft">Left table.</param>
        /// <param name="linkTable">Link table.</param>
        /// <param name="cardinality1">Cardinality 1.</param>
        /// <param name="cardinality2">Cardinality 2.</param>
        /// <returns>New relationship.</returns>
        private OneToManyPhysicalRelationalRelationship CreateOneToManyLeftToLinkTable(LogicalRelationship relationship, 
            PhysicalRelationalTable tableLeft, PhysicalRelationalTable linkTable, 
            OneToManyPhysicalRelationalRelationship.CardinalityForOne cardinality1,
            OneToManyPhysicalRelationalRelationship.CardinalityForMany cardinality2)
        {
            string relTable1ToLinkTableName = String.Format("{0}{1}", Helper.ToTitleCase(relationship.Entity1.Name), Helper.ToTitleCase(linkTable.Name));

            OneToManyPhysicalRelationalRelationship relTable1ToLinkTable = new OneToManyPhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(relationship.Entity1, linkTable, relationship),
                null,
                relTable1ToLinkTableName,
                tableLeft,
                linkTable,
                String.Format("{0}{1}", Helper.ToTitleCase(relationship.Entity1.Name), relTable1ToLinkTableName),
                String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), relTable1ToLinkTableName),
                cardinality1,
                cardinality2);

            return relTable1ToLinkTable;
        }

        /// <summary>
        /// Create a physical one-to-many relationship out of a logical one-to-many relationship
        /// between a table on the right and a link table.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="tableRight">Right table.</param>
        /// <param name="linkTable">Link table.</param>
        /// <param name="cardinality1">Cardinality 1.</param>
        /// <param name="cardinality2">Cardinality 2.</param>
        /// <returns>New relationship.</returns>
        private OneToManyPhysicalRelationalRelationship CreateOneToManyRightToLinkTable(LogicalRelationship relationship,
            PhysicalRelationalTable tableRight, PhysicalRelationalTable linkTable,
            OneToManyPhysicalRelationalRelationship.CardinalityForOne cardinality1,
            OneToManyPhysicalRelationalRelationship.CardinalityForMany cardinality2)
        {
            string relTable2ToLinkTableName = String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), Helper.ToTitleCase(relationship.Entity2.Name));

            OneToManyPhysicalRelationalRelationship relTable2ToLinkTable = new OneToManyPhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(relationship.Entity2, linkTable, relationship),
                null,
                relTable2ToLinkTableName,
                tableRight,
                linkTable,
                String.Format("{0}{1}", relTable2ToLinkTableName, Helper.ToTitleCase(relationship.Entity2.Name)),
                String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), relTable2ToLinkTableName),
                cardinality1,
                cardinality2);

            return relTable2ToLinkTable;
        }

        /// <summary>
        /// Create a physical one-to-one relationship out of a logical one-to-one relationship 
        /// between two physical tables.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="tableLeft">Left table.</param>
        /// <param name="tableRight">Right table.</param>
        /// <returns>New relationship.</returns>
        private OneToOnePhysicalRelationalRelationship CreateOneToOne(
            OneToOneLogicalRelationship relationship, PhysicalRelationalTable tableLeft,
            PhysicalRelationalTable tableRight)
        {
            return new OneToOnePhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(relationship),
                relationship,
                Helper.ToTitleCase(relationship.Name), 
                tableLeft, 
                tableRight,
                Helper.ToTitleCase(relationship.Rolename1),
                Helper.ToTitleCase(relationship.Rolename2),
                this.MapOneToOneCardinality(relationship.Cardinality1),
                this.MapOneToOneCardinality(relationship.Cardinality2));
        }

        /// <summary>
        /// Create a physical one-to-one relationship out of a logical one-to-many relationship
        /// between a table the link table and the table on the right.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="linkTable">Link table.</param>
        /// <param name="tableRight">Right table.</param>
        /// <param name="cardinality1">Cardinality 1.</param>
        /// <param name="cardinality2">Cardinality 2.</param>
        /// <returns>New relationship.</returns>
        private OneToOnePhysicalRelationalRelationship CreateOneToOneRightToLinkTable(LogicalRelationship relationship, 
            PhysicalRelationalTable tableRight, PhysicalRelationalTable linkTable,
            OneToOnePhysicalRelationalRelationship.Cardinality cardinality1,
            OneToOnePhysicalRelationalRelationship.Cardinality cardinality2)
        {
            string relLinkTableToTable2Name = String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), Helper.ToTitleCase(relationship.Entity2.Name));

            OneToOnePhysicalRelationalRelationship relLinkTableToTable2 = new OneToOnePhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(linkTable, relationship.Entity2, relationship),
                null,
                relLinkTableToTable2Name,
                tableRight,
                linkTable,
                String.Format("{0}{1}", Helper.ToTitleCase(relationship.Entity2.Name), relLinkTableToTable2Name),
                String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), relLinkTableToTable2Name),
                cardinality1,
                cardinality2);

            return relLinkTableToTable2;
        }
        
        /// <summary>
        /// Create a physical one-to-one relationship out of a logical one-to-many relationship
        /// between a table the link table and the table on the left.
        /// </summary>
        /// <param name="relationship">Relationship</param>
        /// <param name="tableLeft">Left table.</param>
        /// <param name="linkTable">Link table.</param>
        /// <param name="cardinality1">Cardinality 1.</param>
        /// <param name="cardinality2">Cardinality 2.</param>
        /// <returns>New relationship.</returns>
        private OneToOnePhysicalRelationalRelationship CreateOneToOneLeftToLinkTable(LogicalRelationship relationship,
            PhysicalRelationalTable tableLeft, PhysicalRelationalTable linkTable,
            OneToOnePhysicalRelationalRelationship.Cardinality cardinality1,
            OneToOnePhysicalRelationalRelationship.Cardinality cardinality2)
        {
            string relTable1ToLinkTableName = String.Format("{0}{1}", Helper.ToTitleCase(relationship.Entity1.Name), Helper.ToTitleCase(linkTable.Name));

            OneToOnePhysicalRelationalRelationship relTable1ToLinkTable = new OneToOnePhysicalRelationalRelationship(
                this._currentSchema,
                this.CreateRelationshipPhysicalId(relationship.Entity1, linkTable, relationship),
                null,
                relTable1ToLinkTableName,
                tableLeft,
                linkTable,
                String.Format("{0}{1}", Helper.ToTitleCase(relationship.Entity1.Name), relTable1ToLinkTableName),
                String.Format("{0}{1}", Helper.ToTitleCase(linkTable.Name), relTable1ToLinkTableName),
                cardinality1,
                cardinality2);

            return relTable1ToLinkTable;
        }

        /// <summary>
        /// Map the logical 1:N cardinaltiy to the physical cardinality for one.
        /// </summary>
        /// <param name="logicalCardinality">Logical cardinality.</param>
        /// <returns>New physical cardinality.</returns>
        private OneToManyPhysicalRelationalRelationship.CardinalityForOne MapOneToManyCardinalityForOne(
            OneToManyLogicalRelationship.CardinalityForOne logicalCardinality)
        {
            OneToManyPhysicalRelationalRelationship.CardinalityForOne mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForOne.ZeroOrOne;
            if (logicalCardinality.Equals(OneToManyLogicalRelationship.CardinalityForOne.OnlyOne))
            {
                mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForOne.OnlyOne;
            }
            return mappedCardinality;
        }

        /// <summary>
        /// Map the logical 1:N cardinaltiy to the physical cardinality for many.
        /// </summary>
        /// <param name="logicalCardinality">Logical cardinality.</param>
        /// <returns>New physical cardinality.</returns>
        private OneToManyPhysicalRelationalRelationship.CardinalityForMany MapOneToManyCardinalityForMany(
            OneToManyLogicalRelationship.CardinalityForMany logicalCardinality)
        {
            OneToManyPhysicalRelationalRelationship.CardinalityForMany mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForMany.ZeroOneOrMore;
            if (logicalCardinality.Equals(OneToManyLogicalRelationship.CardinalityForMany.OneOrMore) ||
                logicalCardinality.Equals(OneToManyLogicalRelationship.CardinalityForMany.Exactly))
            {
                mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForMany.OneOrMore;
            }
            return mappedCardinality;
        }

        /// <summary>
        /// Map the logical 1:1 cardinaltiy to the physical cardinality.
        /// </summary>
        /// <param name="logicalCardinality">Logical cardinality.</param>
        /// <returns>New physical cardinality.</returns>
        private OneToOnePhysicalRelationalRelationship.Cardinality MapOneToOneCardinality(
            OneToOneLogicalRelationship.Cardinality logicalCardinality)
        {
            OneToOnePhysicalRelationalRelationship.Cardinality mappedCardinality = OneToOnePhysicalRelationalRelationship.Cardinality.ZeroOrOne;
            if (logicalCardinality.Equals(OneToOneLogicalRelationship.Cardinality.OnlyOne))
            {
                mappedCardinality = OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne;
            }
            return mappedCardinality;
        }

        /// <summary>
        /// Map the logical 1:N cardinaltiy to the physical 1:1 cardinality for one.
        /// </summary>
        /// <param name="logicalCardinality">Logical cardinality.</param>
        /// <returns>New physical cardinality.</returns>
        private OneToOnePhysicalRelationalRelationship.Cardinality MapOneToManyCardinalityForOneForOneToOneCardinality(
            OneToManyLogicalRelationship.CardinalityForOne logicalCardinality)
        {
            OneToOnePhysicalRelationalRelationship.Cardinality mappedCardinality = OneToOnePhysicalRelationalRelationship.Cardinality.ZeroOrOne;
            if (logicalCardinality.Equals(OneToManyLogicalRelationship.CardinalityForOne.OnlyOne))
            {
                mappedCardinality = OneToOnePhysicalRelationalRelationship.Cardinality.OnlyOne;
            }
            return mappedCardinality;
        }

        /// <summary>
        /// Map the logical N:M cardinaltiy to the physical cardinality.
        /// </summary>
        /// <param name="logicalCardinality">Logical cardinality.</param>
        /// <returns>New physical cardinality.</returns>
        private OneToManyPhysicalRelationalRelationship.CardinalityForMany MapManyToManyCardinality(
            ManyToManyLogicalRelationship.Cardinality logicalCardinality)
        {
            OneToManyPhysicalRelationalRelationship.CardinalityForMany mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForMany.ZeroOneOrMore;
            if (logicalCardinality.Equals(ManyToManyLogicalRelationship.Cardinality.OneOrMore) ||
                logicalCardinality.Equals(ManyToManyLogicalRelationship.Cardinality.Exactly))
            {
                mappedCardinality = OneToManyPhysicalRelationalRelationship.CardinalityForMany.OneOrMore;
            }
            return mappedCardinality;
        }
        #endregion
    }
}
