Oliver Kuchies Oliver Kuchies - 6 months ago 46
SQL Question

Storing customised/different user data

If I was to create a data collection application which enabled the user to collect multiple data types what would be the best way to store the customised data?

I. E. User 1 might want to store username, email, address while user 2 might want to store username, password and phone number.

Obviously these data types are different and one table cannot accommodate for the differences according to column based design, what would be the best way to accommodate for this?


This is a very common scenario because many sites will allow you to fill out your profile with as much or as little info as you want (besides required fields), so they need flexible storage. These options come to mind immediately:

Option 1

If you need a way to filter, sort or otherwise process these attributes quickly, a DB table is still your best bet. You don't need to worry about putting different data types in the same column: Have a username column, a phone column, an email column... When you design the table, just allow optional fields to be NULL and leave them empty when you don't have the info.

Option 2

If you just want to store the info, you could use XML or JSON formats. These are really flexible, and will allow you to store any data you want, either in individual files (each user has matching file), in one big file, or again in a DB table with just an id, a username and a generic userdata column that will store the data. When you need the data, just open the file or query the DB and unpack it with simplexml_load_string or json_decode function.