Is It Okay to Define a Foreign Key Referencing ‘user_id’ Column from Every Table?

database-designMySQLschema

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.

Best Answer

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.

Related Solutions

Database Relationships overview appreciated

I've no experience with salesforce, so this is a general answer from a database-design perspective.

I think you've made your model amazingly over complicated. As it stands, you'll have many circular relationships, the possibility to run into infinite loops when traversing relationships, and the possibility of everything linking to everything else (which kind of defeats the purpose of everything.)

If I've understood the above correctly, the following is a simplification of your entity relationships:

A program has many projects. (1:N)
Projects have many contacts, and a contact can be assigned for many projects. (M:N)
Agencies have many programs, and a program can belong to many agencies. (M:N)

The above are the "direct relationships."

Now with this simplification, you can relate Agencies to Contacts through programs and projects. There needs to be no direct relationship between the two. (For example, Contact John is assigned to Project X, which is part of Program Y, which is administered by Agency Z.) Same for contact to contact, and even agency to agency.

Mysql – Recommendation for theSQL table structure (no code)

Try this

userMaster

| USERID |   NAME |
-------------------
|      1 |  Peter |
|      2 | George |

machineMaster

| MACHINEID |   MACHINENAME | NOOFSET | IMAGEURL |
--------------------------------------------------
|         1 |     treadmill |      10 | blahblah |
|         2 | leg extension |       5 |  xyz.jpg |
|         3 |           xyz |       6 |      asd |

planMaster

| PLANID | PLANNAME |
---------------------
|      1 |     xyaz |
|      2 |     bbbb |

planDetail

| PLANID | MACHINEID |
----------------------
|      1 |         1 |
|      1 |         3 |
|      2 |         1 |
|      2 |         2 |
|      2 |         3 |

userPlan

| USERID | PLANID |
-------------------
|      1 |      1 |
|      2 |      1 |
|      2 |      2 |

userPlanDetail

| USERID | MACHINEID | FLAG |
-----------------------------
|      1 |         1 |    1 |
|      2 |         2 |    1 |
|      2 |         3 |    1 |

Only 1 concern is that how you will manage if user selects a new plan. so for this you have 2 approaches

Delete the user existing plan from userPlan and userPlanDetail table
If you want to maintain history of user selected plan you can then you can a have a flag field like which I have used in the above table which denotes that this plan or machine is under current use of the user or not.

If you are following 2nd scenario then while inserting records in the userPlan and userPlanDetail table use INSERT ON DUPLICATE KEY UPDATE.

Other tables are quite simple i don't think so it needs any explanation, post if you have any doubts. Hope this helps...

Best Answer

Related Solutions

Database Relationships overview appreciated

Mysql – Recommendation for theSQL table structure (no code)

SQL FIDDLE

Related Question