The ID not an integer, it’s a string, so it should be stored as a varchar.
You can store up to 65,535 in a single
varchar column. There are larger columns if you need it. The JSON field I mentioned earlier is one of them.
However, the “friends” field you’re looking at is probably not individual friends, but predefined groups of friends. The groups are probably stored in another table. This would make the actual list of friends very smell. Or you could have a one to many relation table sitting in between, which is the preferred method.
How well do you understand normalization?
So, basically you would have your user table which looks like:
user_id | name | some other columns
Then your privacy table. Leaving out the “friends” column. The user id here is who owns this set of privacy settings.
privacy_id | user_id | allow | deny | description | value
Then you would your groups.
group_id | user_id (owner) | group_name | group_description
Then a junction table. This is the table that maps the groups, to the users in that group. The group table stores who owns the group.
group_id | user_id
So to get this data out, you would use JOINS and multiple queries.
SELECT * FROM users u -- grab the users first
LEFT JOIN privacy_settings ps ON ps.user_id = u.user_id -- LEFT JOIN the privacy table
WHERE u.user_id = ?
Then it would be easier to do other queries to get the groups out and populate those into comma delimited fields with SQL. You probably don’t need who’s actually in the group every time you pull the group.
You can see that normalizing it gets very complicated very fast. That’s why the JSON datatypes I mentioned earlier are great. You can just take your entire privacy object and store it in a single queryable blob-like field. This method is very similiar to NoSQL approaches, like MongoDB or CouchDB.