Sql-server – Referencing a single table from all tables in the database

entity-frameworkreferential-integritysql server

At current, I have around 130 tables in my database, anticipating a growth of circa 200 tables in total.

Now I have several tables (well, most of them, actually) that contain properties like CreatedBy / ModifiedBy / DeletedBy, which take an nvarchar(MAX) as their data type and contain the name of the user.

This makes versioning a lot harder, because the name of the user might change during the lifetime of the application.

Now I've thought about referencing each and every *By-Column via a Foreign Key to the users table. However this would result in 600 foreign keys on a single table (expecting the 200 tables with 3 foreign keys per table, if not more).

Are there any drawbacks? What's the best practice?

As a side note (not relevant for the dba aspect of my question, I presume): I'm using Entity Framework in C#, only referencing a single part of it in my code (because I don't want and don't need 600 collections in my User class).

Best Answer

You may want to consider a Type 2 slowly changing dimension table and normalize your user name data. This will give you a better picture of whom is changing the data over time and allow you to use a smaller more efficient surrogate key for your *By columns.

Related Solutions

Sql-server – Clear schema from database without dropping it

I assume you are trying to push your changes back to the database from your entity model? If so you can do that from studio. (Right click in the entity modeler, save changes to the database.)

If you have to recreate it - you can also just change where the EF is pointing in your configuration settings and push the model out to the database if you have create permissions. Really just depends what rights you were given.

Sql-server – Displaying the list of all tables in all database

You need a query for each database against sys.tables.

select 'master' as DatabaseName, 
       T.name collate database_default as TableName 
from master.sys.tables as T 
union all 
select 'tempdb' as DatabaseName, 
       T.name collate database_default as TableName 
from tempdb.sys.tables as T 
union all 
select 'model' as DatabaseName, 
       T.name collate database_default as TableName 
from model.sys.tables as T 
union all 
select 'msdb' as DatabaseName, 
       T.name collate database_default as TableName 
from msdb.sys.tables as T

You can use sys.databases to build and execute the query dynamically.

declare @SQL nvarchar(max)

set @SQL = (select 'union all 
select '''+D.name+''' as DatabaseName,
       T.name collate database_default as TableName
from '+quotename(D.name)+'.sys.tables as T
'
from sys.databases as D
for xml path(''), type).value('substring((./text())[1], 13)', 'nvarchar(max)')

--print @SQL
exec (@SQL)

Best Answer

Related Solutions

Sql-server – Clear schema from database without dropping it

Sql-server – Displaying the list of all tables in all database

Related Question