Modeling User Data for SQL

Question

I am working on an web app in PHP which will work with user data in MySQL.

The difficulty at developing a sql-structure is now to design the relations modular. So that it is easy to delete and add relations to other tables.

My first thought was to create a OneToMany relation from the user table to another table. This other table links than finally the whole data structure down.

user_receiver-table:
user_id | receiver_id | description
1       | 2           | relation to characteristics
1       | 3           | relation to user logs
2       | 4           | relation to picture table

Is this thought generally good?

How do I hide different tables behind one column (and its id)?

Answer 1

The data model reflects your knowledge of the problem you are trying to solve. As your understanding of the problem evolves (and/or external actors impose new requirements), changes in the data model are inevitable. You won't be able to completely automate this process no matter what.

That being said, you should be able to achieve some measure of "generality", using one of the following options:

1. Inheritance

For example:

2. Entity–attribute–value model

A very simplified representation of that would be:

Both have pros and cons, but in a nutshell, the option (1) is less generic but more "type safe".

Answer 2

You can make use of views. http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-views/

However you can not add values to a view when it is related to many tables.

"For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table."