John Soer

Archive for the ‘Design’ Category

The default layout of a sql server database schematic diagram leaves much to be desired, it creates a huge diagram with the most references table located in the midle the one good attribute about the layout is that it avoids relationships crossing one another.

To make matters even worse most  users squeeze the diagram to make it as compact as possible.

This is a nice square diagram that prints out really nice however looking at the diagram it takes a fair amount of effort to determine the table hierarchy.

I prefer the layout the diagram as follows

The diagram is laid out according to two rules:

1. Relationships always run left to right
2. Tables are laid out in columns

This layout provides the following advantages

• Tables not depended on any other table are located in the first column, these tend to be your lookup tables and primary data tables.
• Tables in the second column are depended on tables in the first column, these tend to be your many-to-many tables.
• Tables that are highly depended causes relationships to cross over.
• Tables that have a deep hierarchy are located to the far right hand side of the diagram.

The factory class is a very common programming concept when you are dealing with polymorphism. However the standard examples are always with an Animal interface and a Cat and Dog object which does explain it properly, however in my 10 years of programming I have never ran into a case where I needed a cat and a dog object. In this blog entry we are going to give an example of polymorphism using a database interface and a SQLServer object and an Access Database object. In plain words we are going to create a database object that can be used for both SQL Server and Microsoft Access.

We start out with a IDatabase Interface

interface IDatabase
{
  Connect(string);
  ReturnDataTable(string);
  Close();
}

public class SQLServer: IDatabase
{
  public Connect(string connectionString)
  {
  }

  public DataTable ReturnDataTable(string SQLStatement)
  {
  }

  public void Close()
  {
  }
}

public class MSAccess: IDatabase
{
  public Connect(string connectionString)
  {
  }

  public DataTable ReturnDataTable(string SQLStatement)
  {
  }

  public void Close()
  {
  }
}

public static class DatabaseFactory
{
  public static IDatabase ReturnDatabase(string type)
  {
    if (type == "SQLServer")
    {
      return new SQLServer();
    }
    else if (type == "MSAccess")
    {
      return new MSAccess();
    }
  }
}

Sample use of the Database class

IDatabase myDatabase = DatabaseFactory.ReturnDatabase("SQLServer");
myDatabse.Connect("ConnectionString");
DataTabe myData = myDatabase.ReturnDataTable("SELECT * FROM Table");
myDatabase.Close();