Possible Circular Design Issue? (1 Viewer)

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
Before I get too far into working on my "Funding Applications" database, I wanted to confirm if the E-R diagram contains a circular design problem.

Description of the db: It's supposed to track Funding Applications for various Projects under the control of either a single Department or multiple Departments. Projects and Departments have a M:N relationship.

Each Department has multiple Reporting Periods and during each Reporting Period, Departments will issue at least 1 Report and sometimes more than 1 Report.

Funding Applications for each Project Department combination are prepared, but not necessarily submitted. When they are submitted, they are "published" in the Reports issued by the Departments during the relevant Reporting Period.

The E-R Diagram I have come up with is in the attached file. If you think there is a circular "issue" or a better way to reflect the process, I would be interested in reading your comments. Thx.
FundingAppsDesign.jpg
 

Minty

AWF VIP
Local time
Today, 16:10
Joined
Jul 26, 2013
Messages
10,354
I think you should simply have your tblDdepartments listed twice (aliased), once for each place it's used as a lookup.

No different to using an employee table more than once somewhere in a system.
 

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
Thanks, Minty. So something like this ?
Funding Apps REVISED.jpg
 

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
The revised ER diagram in my previous post seems to be "re-setting" itself when I re-open the diagram.

That is, the relationship line between the alias Department Table and the Reporting Periods Table has disappeared; and the old (original) relationship line between the Departments Table and the Reporting Periods Table has re-appeared.

Any idea on how I can get the ER diagram to stabilize and stop making these changes back to the original design?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:10
Joined
Feb 28, 2001
Messages
26,999
Your direct question from post 1 didn't get directly answered. You had two paths that led from tblDepartments to tblReportsFundingApps. This is not good because what happens is the query wizards will get terribly confused and might build the wrong relationships.

Your second diagram with the duplicate department table references was the right idea. I have not seen the automatic reversion that you mentioned so can't address that.
 

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
I'm happy to follow the recommendation from Minty and I 100% agree that the revised ER diagram is the way to go.

But I'm stalled for the time being. Access file is attached if someone can assist.
 

Attachments

  • Practice3.zip
    33.3 KB · Views: 91

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
Is it possible that using 2 copies of a table in a Query Design Window is OK; but that it is NOT OK in the Relationships Pane ? Perhaps that is the reason why the relationship lines in my relationships pane appear to be unstable.
 

strive4peace

AWF VIP
Local time
Today, 11:10
Joined
Apr 3, 2020
Messages
1,003
hi wilderfan,

my opinion is that the first diagram is better. Do what is comfortable for you. I don't see a problem with the circle. To me, this makes the relationships more clear, and another copy indicates there is a table with 2 DeptID fields. We all have our own ways of doing things, and we all have our own opinions -- just ask a question about naming fields to see a real brawl!

Rather than a multi-field primary key in tblReportFundingApps, I would opt for an AutoNumber primary key with a Unique index on the combination of ReportID and FundingAppID
 

strive4peace

AWF VIP
Local time
Today, 11:10
Joined
Apr 3, 2020
Messages
1,003
hi wilderfan,

I've got to run off to do some training, but opened your database real quick and looked at a few table designs. Two suggestions for text fields

1. set field SIZE more appropriately instead of leaving at default of 255 characters
2. set Unicode Compression to Yes

and one for numeric foreign keys:
1. remove default value of 0 (zero) that Access automatically supplies when you create a numeric field
 

Dreamweaver

Well-known member
Local time
Today, 16:10
Joined
Nov 28, 2005
Messages
2,466
For a small database like in images you can do that but when you get to 40-50+ tables system the relationships window can become very cluttered then adding all those circular ref tables on top it turns a manageable system into a messy one, my project management system has 145 tables, the only ones I add are self reference tables like a employee manager relationship
I prefer to create them in the query window as and when I need
I will post a ERD later of the system I'm planning at the moment
 

Dreamweaver

Well-known member
Local time
Today, 16:10
Joined
Nov 28, 2005
Messages
2,466
This is what Access does to some my nicely organised relationships window and thats after I move some so I can work with it, Any help there would be appreciated
2020-04-07.png

This is what I'm working on at the moment it's about 45% done
Animal Sanctuary.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:10
Joined
May 21, 2018
Messages
8,463
This is the second thread recently where it shows a lot of confusion about the differences between relationships and joins. See @isladogs post for a very good explanation.


Seems to be a lot of confusion about circular references, and their impacts. See this discussion for good examples and when there is concern and when there is not a concern. Most of the time there is no issue with data integrity.

1. Adding an alias table is not a solution for circular references. That does absolutely nothing, so disregard that recommendation. Relationships are about referential integrity not about joins. The default join type is an added feature of the relationship window.
2. Doing joins in the query window has nothing to do with relationships and referential integrity
3. In the current OP's design you will not have data integrity issues.
4. To fix a circular reference requires a table change (if really needed). In your case get rid of the department ID in tblReportingPeriod. As far as I can tell you cannot have a reporting period without a report, without a fundingapp, without a project, without a department. If I am wrong about that, then leave it as is and do not worry about it, I cannot see any data inconsistencies in this case.
 

wilderfan

Registered User.
Local time
Today, 09:10
Joined
Mar 3, 2008
Messages
172
I appreciate all the suggestions. I especially liked the Mendip article and this excerpt:

"... there is some disagreement amongst developers about the use of table relationships. Some developers apply them rigorously to any tables with linked data. Others hardly ever use them. "
 

isladogs

MVP / VIP
Local time
Today, 16:10
Joined
Jan 14, 2017
Messages
18,186
Glad you found my article useful @wilderfan. For info, I fall somewhere in between those two groups!
Thanks for providing the link @MajP
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:10
Joined
May 21, 2018
Messages
8,463
... there is some disagreement amongst developers about the use of table relationships. Some developers apply them rigorously to any tables with linked data. Others hardly ever use them.
Relationships are about enforcing referential integrity. In many databases creating an orphan may not have large impacts or any impact, in others it can be critical. Transactional databases, inventories, accounting etc could have severe impacts if a record is orphaned. One I have been working with a lot lately is hierarchical self-referencing tables.
In this case if a single record is orphaned an entire branch of data would be lost to the algorithm. Also deleting of branched child records would be extremely difficult without cascade deletes. You could have to do a recursive call.
So it is more than just preference, it is also knowing the impacts of what could possibly happen if referential integrity is not maintained.
 

Thales750

Formerly Jsanders
Local time
Today, 12:10
Joined
Dec 20, 2007
Messages
2,061
My backends only contain tables and maintenance objects.
One thing I do is create is Saved Queries to use in the Relationship Form. It allows you fine tune the display.
 

Thales750

Formerly Jsanders
Local time
Today, 12:10
Joined
Dec 20, 2007
Messages
2,061
Crossposted at https://www.utteraccess.com/forum/index.php?showtopic=2057523&st=0#entry2748540

EDIT
@MajP gave a better answer as to when/why relationships should be created. I will ALWAYS do so where referential integrity is needed.
Agreed. I use the Relationship Form as visual tool to keep it all straight, I agree I do not use referential integrity unless it will save a lot of cleanup or control code. When you switch to SQL you will have to address those areas, much better to have tighter code if possible.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:10
Joined
Feb 19, 2002
Messages
42,970
The revised ER diagram in my previous post seems to be "re-setting" itself when I re-open the diagram.
To get the modification to stick, right click on the join line and delete the relationship. Then when you delete the table from the diagram, it won't come back. Just deleting the table from the diagram does NOT delete the relationship and the relationship is what is bringing the table back:geek:
 

Users who are viewing this thread

Top Bottom