Codeigniter: how should I restructure db schema?
- by Kevin Brown
I don't even know if that's the right term.
May it be known that I'm a major novice!
I have three tables: users, profiles, and survey. Each one has user_id as it's first field, (auto-increment for users), and they're all tied by a foreign key constraint, CASCADE on DELETE.
Currently, for each user, let's say user_id 1, they have a corresponding db entry in the other tables. For profiles it lists all their information, and the survey table holds all their survey information.
Now I must change things...darn scope creep. Users need the ability to have multiple survey results. I imagine that this would be similar to a comment table for a blog...
My entire app runs around the idea that a single user is linked to a constraining profile and survey.
How should I structure my db?
How should I design my app's db so that a user can have multiple tests/profiles for the test?
Please assist! Any advice, information and personal-knowledge is appreciated!
Right now the only way I know how to accompany my client is to create a pseudo-user for each test (so unnecessary) and list them in a view table (called "your tests")-- these are obtained from the db by saying: where user_id=manager_id
