(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:
-----
[Table] users
key: user_id
+main_view_id
+user_name and other relevant user fields

[Table] views
key: view_id
+user_id (Foreign key with relationship back to users::user_id)
+view_name

[Table] components
key: component_id
+component_name

[Table] view_components
+view_id (FK to views)
+component_id (FK to components)
+orderInView
-----
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.

-PatP

That kind of makes sense, and is kind of similar to how I was setting it up. I'd never run across that, so I guess my database design books are either too basic or too abstract (i.e. not practical).

Would it make sense to make a separate table as such
[Table] users_mainviews
user_id (FK to users::user_id)
system_view_id (FK to views::view_id)

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.

-PatP