Im trying to start building a dashboard filled with user set options such is the user profile private/public and adding "Subjects" with their own variables that stay on database.
I already have table for users. All works fine.
With these properties:
- user_id
- user_name
- user_email
- user_pass
How do I proceed if I want to system that user can add text such as their favourite color into the database without creating new table for every new user that register into the system.
It sounds like you're asking how to create a table structure in which the user can define their own schema. For example, if users are defining their own key/value pairs of information and each user would be different.
There are a couple of approaches to take...
First, give considerable thought to what you're doing. Does the user experience really call for this? Or would some free-form text fields suffice? What else in the system needs these to be individually tracked key/value pairs?
You could use a schema-less database, such as a document store in which any given record can have any serializable structure.
In a relational database, this generally boils down to simply storing the key/value pairs. Something like this:
Users
----------
id (PK)
username
password
email
UserValues
----------
id (PK)
userid (FK to Users)
key
value
With this, any given user can store as many key/value pairs as they like. And you can continue to expand on this structure to add more user data structures if necessary. One significant drawback to this is that everything becomes "stringly typed" which means that if a value should be numeric then your code needs to be robust enough to validate that anywhere the value is used, because in the backing data everything here is just going to be strings.