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

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.

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.

StackExchange Database Administrators Q#164217, answer score: 2