1

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).

1

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.

-1

I wouldn't use an actual FK constraint on every table. If you are controlling what happens to that table in the centre of your design, you could prevent deletes and updates to the PK easily enough. Otherwise, every time you insert a record in any table you need to confirm the row is there in the centre table. But using the key values - by all means. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.