My first codesmith project

I have a lot of tables in my database that i am converting to a codesmith asp.net app. every table has a CreatedBy and ModifiedBy column that link back to a users table however there is no relationship in the database (vb6 app that assumed that the links were there).

Is there a best practice for this scenario? there is about 50 tables in the database which would mean creating 100 relationships and creating some circular references which i don't want to do. I also don't want to have to go back to the database to get the firstname and lastname of every record when filling out a gridview.

I don't think you need to add foreign keys since all of the fields are named the same. 

You can put a check in as you are looping through the columns and provide specific functionality for these two columns.

 Something like

<%@ CodeTemplate Language="C#" Debug="True" TargetLanguage="Html"%>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="Table that the object is based on." %>
<%@ Assembly Name="SchemaExplorer"%>
<%@ Import Namespace="SchemaExplorer"%>
<%@ Import Namespace="System.Data"%>

<%
SchemaExplorer.TableSchema userTable = SourceTable.Database.Tables["User"];

foreach( ColumnSchema column in SourceTable.Columns )
{
 if( column.Name == "ModifiedBy" || column.Name == "CreatedBy" )
 {%>
  //do what is required
  <%}
}%>