title: Column name collision in SQL Server view joining database architecture
body: <p>First of all, sorry the way I write, bad English here.</p>
  
  <p>Please, consider the following scenario:</p>
  
  <p>I made my database architecture combining views as such:</p>
  
  <ul>
  <li>Entity <code>City</code> has the columns { <code>city_id, city_name, state_name, ...</code> }</li>
  <li>Entity <code>User</code> has the columns { <code>user_id, user_name, user_login, city_id, ...</code> }</li>
  </ul>
  
  <p>Each entity has a view, the <code>city</code> view does not have FK to any other entity, so its a simple select.</p>
  
  <p><code>User</code> entity have one FK to <code>city</code> entity, so it makes a join with the <code>city</code> entity in the view.</p>
  
  <p>As the following example:</p>
  
  <pre><code>create view vw_user as
      select user.user_id,
             user.user_name,
             user.user_login,
             vw_city.*
      from user (nolock)
      inner join vw_city (nolock) on vw_city.city_id = user.city_id
  go
  </code></pre>
  
  <p>So if you have an entity like user_access with a FK to user, the view of <code>user_access</code> will have an inner join to <code>vw_user</code>, and it will bring up all the columns from <code>user</code> and <code>city</code> entities.</p>
  
  <p>In the end, I just make a query on the view, and it returns the full entity with all the foreign references.</p>
  
  <p>This works great, and make the maintenance procedures very easier, I have a stored procedure that recompile all the views if any change is needed to one of the entities.</p>
  
  <p>But, it has a problem, and I discovered this in a very bad situation, the system that uses this architecture is working very fast and smooth already, and I need to join two entities that share the entity city for example, so it generates a collision of columns, SQL Server does not allow this in views, you can execute a query with collision with no problem, but you can't do it in a view.</p>
  
  <p>So, this is my problem, I need to find a way to fix this, but I can't find an answer to it myself.</p>
  
  <p>The only thing I ended up doing is making a hard-coded view with the columns renamed.</p>
  
  <p>But I want a solution for the entire architecture, something permanent, like an upgrade.</p>
  
  <p>This architecture is followed in code, with C#, so there is a dependence on the column names.</p>
  
  <p>When I read the data from the query, I need to get the data from the column name, so this code can be reused with other classes, doing the same thing that the view does in the query, in the c# code.</p>
  
  <p>So, getting data with ordinal is out of the question.</p>
  
  <p>Any ideas?</p>
  
  <p>Thanks, and sorry, but this is very hard to describe.</p>