Is it okay to define a foreign key referencing a ‘user_id’ column from every table?

Question

I have a multi user site which in which

• a user can have many restaurants,
• each restaurant can have many menus and
• each menu can have many items.

For security purposes, I was going to add a user_id foreign key making a reference to the user.user_id column from each of the pertinent tables. That way, when updating the data, I can use the id of the row they are updating in combination with their user_id (stored in a session at the application program level) to ensure they don't maliciously update or view another users content.

Questions

• Is this a common practice?
• Is it still normalized to do this?

Responses to comments

The code is only running on a website I control. But I want to prevent a user from accessing a row of data that he/she does not own, based on his/her user_id value. For instance, at the application program level, if they send a GET request to /menus/1 it will retrieve the row from the menu table with the id value of 1.

Answer 1

For your first question, I've managed many systems that do this. Some actually defined that Foreign Key relationship. Others had the "foreign key" field, but didn't define it in the database as key. This led to interesting quirks like data loaded outside of the application (pre-populated rows) having "updated by" fields with values that weren't users in the user table. Typically the tables had "ChangedBy" columns that stored User IDs and "ChangedOn" fields that stored a date/time for the change event.

For your second, why wouldn't it be normalized, if you set up the Foreign Key relationship back to a users table? You aren't duplicating the user information at the menu, restaurant, etc. tables, right? Just the foreign key ID field.

Answer 2

It depends on the type of authentication you are doing. A common practice in SQL Server is to use only windows authentication, then to use SUSER_SNAME() as the default when saving to a User_ID column. I am not certain which MySQL equivalent is correct, but based on a quick Google it is likely either

CURRENT_USER
USER()

Then you can check the value saved in the table against the value for the person changing the record.

And no, this does not break database normalization. As a practice it is quite common to include metadata like AddTime, AddUserID, LastChangeTime, LastChangeUserID. In fact in some environments I have worked in, it was a requirement that all tables have these columns for audit-ability.