(Note: I've changed the exact names of tables and fields to make it more easily understood).
I've got a database design problem that I can't figure out the appropriate way. I'd rather follow the rules of data normalization, unless it makes my queries unnecessarily slow (through too many joins).
There are many users. Each user can select from predefined components and place them in a view. Each user has many views. Each user also has a "main" view that shows all of the components selected for any view. A user may just use the main view if he/she chooses.
So a user puts a component into his "project A" view, and it's also visible in the "main"/"All projects and uncategorized" view.
Here is what I have so far:
+user_name and other relevant user fields
+user_id (Foreign key with relationship back to users::user_id)
+view_id (FK to views)
+component_id (FK to components)
If I do it this way, I'm required to create a default view when I create a user. Whenever I add insert into view_components for a view that is not the "main" view, I also have to insert into view_components for the "main" view.
Alternatively, I could not have an actual view for the "main" view. When the "main" view is chosen in the application, I could do a select all where user_id = the requesting user. However, this doesn't allow the user to have a main view when no other views exist, or add components to the main view only.
I'm stumped about the proper way to do this, and no examples or anything I read in books seems to be able to really help me figure out what to do here. I appreciate any help you can give. If I wasn't clear, please ask for clarification.
The common way to handle this kind of problem is to create a "system" view that is often named "Unclassified" if the user is allowed to see it. This view is not user defined, it is application defined, so the user is not permitted to drop or directly manipulate this view.
Any components that the user wants to reference that are not contained within any user views are added to this system view. In some systems, any component that the user references is added to this system view, whether it is in a user view or not.
No, I'd just have a "view type" column in your view table. Being a purist, I'd also add a "view type" table and make the view.viewType be a foreign key to the new "view type" table.
Then all you need to do is to make sure that the system only plays with system views, and the user only plays with user views. The system can then store some or all components into its view(s), and the user can store whatever components suit them in their own user views.