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!

So you create a table of ProposalRoles that holds userIDs and what their relationship to the proposal is.
What is your "Reviews" table for?

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?

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.

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.

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?

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.

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

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

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.

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!