User-specific database records

This is more of a theoretical questions. Anyways, Assume that I have a database (50 tables) And Data in ALL of those tables should be hold specifically to one user. What I mean is, usually we do foreign keys for each table that is user-specific. and retrieving data using something like:

select Column1,Col2,Col3 from Table where UserId=@UserId

This clutters queries significantly. 开发者_如何学编程Is the other way of storing such data?

---

What you're referring to has a name. It's called multi-tenant. The link gives some general information on multi-tenant structures.

This all depends on your RDBMS though. Oracle provides a tool which makes this very easy. The

WHERE User_ID = @User_ID 

becomes a policy which you can apply to tables. You also create a protected stored procedure to update @User_id to ensure that it cannot be changed without authority. Then every query to a table with such a policy adds the where clause in the background. It's automatic and there's no way to avoid it.

---

There might be other ways, but I wouldn't say they are better. If every record in every table needs to belong to a specific user then you need a way to define that.

A possible method to reduce the number of UserId columns would be if you have child tables and you simply assumed that the parent table defined ownership and that it was implied that it's child records belonged to that user.

Example:

CarMake:
MakeId
MakeName
UserId

CarModel:
ModelId
MakeId
ModelName

Notice that CarModel does not have a UserId column, but you could assume that it inherits the same UserId of the parent CarMake record. If you cannot assume that children should always inherit the parent UserId then your original method is best.