Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Difficult db design problem

    (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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Aug 2007
    Posts
    10
    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)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •