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)?

just remember that using lots of JOIN can slow down performance — s_p
– s_p, Commented Apr 1, 2012 at 16:50
Hi, how do you menage to relate users to other tables without hardcoding. So that you can add and remove relations? — bodokaiser
– bodokaiser, Commented Apr 1, 2012 at 17:15

Branko Dimitrijevic · Accepted Answer · 2012-04-02 11:47:19Z

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:

enter image description here

2. Entity–attribute–value model

A very simplified representation of that would be:

enter image description here

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

answered Apr 2, 2012 at 11:47

Branko Dimitrijevic

52.3k12 gold badges103 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bodokaiser Over a year ago

Hi, does sounds good. So the point is to use the same FK for all tables. Do you know if this is doctrine compatible? Regards

Branko Dimitrijevic Over a year ago

@user1246987 Sorry, no. I'm not familiar with the Doctrine project, nor with PHP for that matter - this answer is purely from the database modelling perspective.

bodokaiser Over a year ago

Ok thank ya. Will look around how I could implement the pattern into an ORM. Regards

bodokaiser Over a year ago

Ok I know decided to create a one to one relation from the User-Table which contains all login-related data to User-Composite what contains all private data like name, birth, and so on. Than I can work with the user-composite and have less dependency to the user component

Dickriven Chellemboyee · Accepted Answer · 2012-04-01 19:29:49Z

1

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."

answered Apr 1, 2012 at 19:29

Dickriven Chellemboyee

933 silver badges8 bronze badges

Collectives™ on Stack Overflow

Modeling User Data for SQL

2 Answers 2

1. Inheritance

2. Entity–attribute–value model

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1. Inheritance

2. Entity–attribute–value model

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related