Circular References in Database Design - Should they be avoided?_问答_开发者

I am currently developing a database via MS Access 2003 and got stuck at a circular reference problem. Basically, it comes down to the following relationship triangle (it is a simplified form of my relationship table):

                     Positions
                 oo            oo
                /                \
               /                  \
              /                    \
             /                      \
            /                        \
           /                          \
          /                            \
         /                              \
        /                                \
       /                                  \
      oo                                  oo
  Employees  oo -------------------- oo Software,

where Positions, Employees and Software are the tables, and "oo-------...-------oo" displays the many-to-many relationships between them.

In short, all 开发者_运维技巧of the employees in a company are assigned to specific positions (some of them are assigned to more than one), and have permissions to use specific piece(s) of software based on their position(s). However, there are exceptions, and some of the employees are granted to use a few number of other software packages, in addition to what they are allowed to according to their position(s).

The question is, is it OK to allow a circular relationship in this kind of database? Are there any workarounds that do not require denormalization?

Thanks in advance, VS.

Your diagram is elliptical in the sense that you've left out the N:N join tables between all your entities. Those make a HUGE difference in regard to the side effects of circular relationships. Direct 1:N relationships with CASCADE DELETE on can cause real problems, and potential deadlocks. But with the N:N tables in between, you shouldn't have that problem, as CASCADE DELETE would run only "downhill" from the 1 table to the N, and not back up the chain from the N:N table to the other parent table.

It seems to me that this is a common problem, isomorphic with the address problem, i.e., a person can have a personal address and inherit an address from the employer, and @Saif Khan's solution of eliminating the software inheritance from the position is a form of denormalization, in that you've collapsed two complex entity relationships into a single one. I never know how to model this, not because of potential circular relationships, but because of the performance issues (and non-editibility) that come from assembling a single result set of all software/addresses, which requires a UNION. I would be tempted to use a trigger to duplicate the software inherited from the position with a record linking the person to the software.

Prior to A2010, this was not possible at the engine level in Access/Jet/ACE, but A2010 added table-level data macros which can be used to implement the equivalent of triggers. This could be a case where that new feature could allow you to implement this structure with triggers.

But I'm not sure I'm comfortable with duplicating data, even though triggers would allow you to keep the duplicated data in synch at the engine level.

You could avoid it by generating new position for each of the exceptions. A boolean flag could then be added to the position to differentiate between real and exception generating positions, if required.

You need to properly normalize the DB. IMHO - I'd not use a relationship in the positions table. Here is what I'd do

Tables

Employee
Software
EmployeeSoftware
Position

The "POSITIONS" table in your case, i assume, is your roles. Note the DB should be used as storage and very minimal business logic should be placed there. That being said, ...let me continue

There will be a relationship between Employee and EmployeeSoftware (empid present as foreign key in EmployeeSoftware. The same for Software and EmployeeSoftware (softid present as foreign key in the EmployeeSoftware.

The application first checks if a person is in a proper position (POSITIONS) table before inserting a record. For an additional DB check you can add a check contraint on the EmployeeSoftware to check the POSITIONS DB before...there then need to be a Relationship between Software and Positions.

I think this database design is getting too complicated because of the way of handling the exception,

"some of the employees are granted to use a few number of other software packages, in addition to what they are allowed to according to their position(s).

Don't try to directly link an Employee to software.

I would just create another position because the main purpose of position in this case is to determine software access. Even if one person has a unique list of software, they will get replaced in the future and that person can just be assigned the same position(s).

Querying will be easier. As David-W-Fenton pointed out, you're going to have to use a lot of unions to find out who can use what software or vice versa.