By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,468 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Relationships

P: 38
Hello everyone, first of all let me thank you for looking at my question. My question is I have 3 tables total, one table is a 6 Month Review, 12 Month Review and Team Members. The 6 Mo and 12 Mo are exactly the same, the consist of ReviewID which is the autonumber and teammemberID which is where I related it to the Team Member table which is the Prim for the Team Member table. My problem is that when I relate these it says that I have broken referential integrity because something on the 6 mo/12 mo doesn't relate and I have no idea why I am receiving this message. The 6 Mo/12 Mo table has a variety of yes/no fields with many memos, but other than that it seems fine. The Team Member table has TM info such as dept. seniority # and things of that sort.
Apr 13 '09 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi. You don't tell us what the data type of the Team Member primary key is. If it is an autonumber then the matching data type in your reviews tables should be a long integer.

To find non-matching rows which would break relational integrity create a new query in which you join the review table to the team member table. Select the join and change it to a left or right join as appropriate - the one that says include all rows from the review table and only the one from the team member table that match. Include suitable fields from your review table (and particularly the team member ID secondary key). Include the team member primary key from the Team Member table, and in its criteria box enter Is Null. When you run the query you will see only the rows where there is no match for the team member ID in your review table to your team members table.

As a side comment, I am certain that your tables are not in completely normalised form. If you have two tables that are identical except that one is for a 6 month review and the other is a 12 month review you have mistakenly confused an attribute - the review period - with the need for another table. You should just have one review table, which includes as an attribute a definable review period that in turn is specified in a separate review periods table.

What happens if you decide you need another review at 9 months? You should never need to add another table...

We have a useful introductory article on data normalisation and table structures which you may find of interest.

The main thing to remember is that in a normalised table all attributes - the fields of that table - must be dependent solely on the primary key and nothing but the key. Any other kind of dependency - such as the implicit one about review periods, which is hidden away in your current design by using multiple tables - requires further decomposition before the design can be considered normalised.

-Stewart
Apr 14 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.