2

I want to create a structure where there is a table with a userid and username and each userid gets it's own table. I wondered if there is a way to add a third column with the type being it's own table. If not what is the best way to go about this.

Kyro Productions
  • 21
  • 1
  • 2

2 Answers2

1

I think the best way to saving user attributes is create a usermeta table. attention to following structure:

Users:

user_id | user_name | user_mail
--------------------------------
1       | ali       | me@ali.com
2       | mahdi     | me@mahdi.ir

Usermeta

meta_id | user_id | meta_key | meta_value
-----------------------------------------
10      | 1       | points   | 10
11      | 1       | address  | london
12      | 2       | address  | iran
13      | 2       | is_vip   | true

In this form you can add any attribute for any user that you want. it's flexible to save no-predefined attributes. in this structure you have not redundancy of data.

You can use dynamic column also.

MahdiY
  • 1,269
  • 21
  • 32
  • FWIW, whe using an EAV, I like to split tables according to data type - so, I might have a table of date type things, a table of integer type things, and a table of string type things. – Strawberry Jun 25 '17 at 21:46
  • This is the way also WordPress does it. Its a great way to go in many cases. – Blackbam Jun 25 '17 at 21:47
  • @Strawberry that's correct. but it's a general structure. if you consider data type, create multi usermeta tables! – MahdiY Jun 25 '17 at 21:55
  • @Blackbam Yes, it's WordPress table structure. – MahdiY Jun 25 '17 at 21:57
0

I don't think this would be a good thing to do. You'd better create a table for your users and then for example a settings table with a UserId column.

This way you can reference to the user specific properties using joins. For example:

Table Users with columns: UserId and Username

Table Settings with columns: UserId, Setting, Value

If you then want to get those combined you could use this sql:

SELECT Users.UserId, Username, Setting, Value 
FROM Users  
    INNER JOIN Settings ON Settings.UserId=Users.UserId
neuzehie
  • 152
  • 1
  • 15