Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Question Not Sure How to Design without a Circular Reference

    I'm working on an Oracle database design and I'm not sure how to avoid a circular reference. Unfortunately, this is a small part of a big database, and this would be one of many circular references, making it quite messy. How can I do this correctly?

    The issue is that there are many different types of users in the system, and each user can play a different role at different times. This is for a system that handles research proposals. A user may be a Researcher when associated with proposal ID 123, for example, but that same user may be a Reviewer for proposal ID 456.

    TABLES
    --------
    Users
    Proposals
    Reviews

    A proposal will have a review associated with it (the review scores and comments, etc.) as well as multiple users with different roles (Researcher, Reviewer, etc.).

    I'm not sure about the best way to go about this. Any ideas? Thanks!
    Last edited by Glowball; 03-13-09 at 17:36. Reason: Clarification

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    So you create a table of ProposalRoles that holds userIDs and what their relationship to the proposal is.
    What is your "Reviews" table for?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    I was thinking about that but it could get a little weird. These tables are just examples -- in reality, there are lots of these types of tables. For example, there are research proposals in all cases but each one will have many reviews (so many reviewers) along with multiple managers, in-progress interviewers, funding administrators, and other users who will be involved with each project. Any given user can have any number of different roles when associated with each project.

    So if I went with a "ProposalRoles" table I would tie the users table to the projects table, storing the role in that many-to-many table. But I would also need to store the ID of the review, interview or other record that exists in another table.

    For example, a single project may have 5 reviews associated with it. The "reviews" table will hold the actual review (text and ratings from the reviewer). So I would need to also store the review ID in the ProposalRoles table.

    Would I make an actual relationship (foreign key) between the ProposalRoles table and the reviews table? That doesn't seem right. And there's my circular reference.

    Thoughts?

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    These tables are just examples -- in reality, there are lots of these types of tables. For example, there are research proposals in all cases but each one will have many reviews (so many reviewers) along with multiple managers, in-progress interviewers, funding administrators, and other users who will be involved with each project. Any given user can have any number of different roles when associated with each project.
    So just have a more generic table with a field to say what type of relationship the user has with the project. You could have a lookup table for the types of relationship and perhaps have any useful rules detailed there. It will save you a bunch of similar tables littering your database and you can expand it at any point by adding a new type.

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    So say I have these tables:

    users
    projects
    rel_users_projects (the relator table)
    reviews
    interviews
    funding

    For any one given project there can be multiple entries in the last three tables (funding information for each year of a multi-year project, for example). There are many people associated with the project, all with different roles.

    I understand what you're saying about the relator table and how I can say who is associated with the project and what their relationship is, but the part I don't know how to do is to also associate that particular user with an entry in one of the last three tables above. I'll have to put another field in the relator table that says which review they're associated with, as an example.

    So do I also make a relationship between the relator table and the reviews table? This is my original issue -- I'm not sure how to make a relationship there without making a mess and many circular relationships tying that relator table to other tables in addition to the projects table.

  6. #6
    Join Date
    Mar 2009
    Posts
    7
    I guess I can boil this down to a pretty simple question (well, simple for people other than me): if I have a situation where I can associate tables with each other and make circular relationships, what are the rules I should follow in order to make a good design? This is the data, really boiled down. Where should foreign keys be, and where should it just be another column in the database?


    Code:
    USERS
    -----
    User ID    Name
    -------    -----
    1          Bob Jones
    2          Jane Smith
    3          Ralph Johnson
    4          Missy Fredricks
    
    
    PROJECTS
    --------
    Project ID    Researcher ID    Title
    ----------    -------------    -----
    1             1                Cool Title for a Cool Project
    2             2                Another Cool Project Title
    
    
    REVIEWS
    -------
    Reviewer ID    Project ID    Year    Rating    Comments
    -----------    ----------    ----    ------    --------
    2              1             2008    2         Too expensive
    3              1             2008    5         We need this research
    
    
    FUNDING
    -------
    Admin ID    Project ID    Year    Amount
    --------    ----------    ----    ------
    4           1             2007    100000.00
    4           1             2008    200000.00
    Last edited by Glowball; 03-16-09 at 15:53. Reason: Attempting some formatting

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The design above looks fine as it is.

    It would be difficult to have the generic relationship table I mentioned earlier because you have all the extra data associated with a relationship ie funding, feedback etc.

    The FK's just go from the user id's in each table back to the user table and again on the project id's in each table to the project table.

    Could you give an example of a circular reference that might happen in your existing design.

  8. #8
    Join Date
    Mar 2009
    Posts
    7
    So I would have a circle for each table that links through users and projects, right? I would end up with quite a few of these loops. Is this okay (this link is to a simple image, a screen capture from my modeling software)?

    http://www.glowball.com/model_example.gif
    Last edited by Glowball; 03-16-09 at 16:31. Reason: Added image

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Glowball
    So I would have a circle for each table that links through users and projects, right? That seems off to me...
    So to list all the reviewers of a project you just join the users table and the reviewers table on a given project id - what's wrong with that? I'm probably being a bit stupid but I can't see an issue here. Can you be a bit more specific about the problem (just for me).

  10. #10
    Join Date
    Mar 2009
    Posts
    7
    I was working on my model and I kept adding all of these loops and it started to look very, very wrong. Am I just seeing an issue that isn't there?

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I was working on my model and I kept adding all of these loops and it started to look very, very wrong. Am I just seeing an issue that isn't there?
    Well, if you can't point out a problem, then I guess there's not much to fix though I still can't see the loops you're talking about. But essentially there's nothing wrong with joining tables together.

    I'd suggest just listing a bunch of things you need to represent in the database, or report on, or do as a "task". Then see how you'd do these things with your design. Database design tends to be a bit of an iterative process. If something still looks a bit iffy then we can fix things.

  12. #12
    Join Date
    Mar 2009
    Posts
    7
    Thanks for your help, I'll keep at it. If you don't see an issue with that drawing I linked to then I guess I'm good!

Posting Permissions

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