{"id":725,"date":"2013-07-31T03:14:00","date_gmt":"2013-07-31T07:14:00","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=725"},"modified":"2016-07-08T22:48:58","modified_gmt":"2016-07-09T02:48:58","slug":"circular-reference-well-kind-of","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=725","title":{"rendered":"Circular Reference (well, kind of)…"},"content":{"rendered":"

You sat down to build your database and things were going great.\u00a0 You made a table for Members and one for Members Contacts.\u00a0 Only you now realize that the Members Contacts can also be Members.\u00a0 At first you think… that will be okay.\u00a0 But then you go to build your forms and realize this is a problem.\u00a0 If you create a UNION query to show all the Members, you can\u2019t edit the information AND you notice that some Member\u2019s ID <\/b>is the same as some Member Contacts ID<\/b>.<\/p>\n

This is when you need to think outside the box.\u00a0 You can store all your Members, Company\u2019s, Contacts and Individuals, in the same table and still keep track of Member\u2019s Contacts which will make querying much easier.\u00a0 But let\u2019s be clear, this is the exception to the rule, not the rule.\u00a0 Normally, I would recommend a table for Company’s and a seperate table for Contacts.\u00a0 However, we all know in the real world the *standard<\/strong>* does not always apply!<\/p>\n

For this example we\u2019ll use the table as outlined below (To see the complete Data Model and download the Sample Database, click here<\/a>)…<\/p>\n

tblMembers
\n<\/b>mActive
\nmMemberID (PK)
\nmRegistrationID
\nmAttachedTo (circular reference to Member ID in this<\/b> table)
\nmCompanyName
\nmRegistrationID
\nmTitle
\nmFirstName
\nmLastName
\netc…<\/p>\n

In the table we\u2019ll enter…<\/p>\n

\"tblComplaints\"

Figure 1<\/p><\/div>\n

As you can see Access Diva (Company) has a MemberID<\/strong> of 1.\u00a0 Gina Whipp, who works for Access Diva but is also a Member has a MemberID <\/strong>of<\/strong> 2.\u00a0 In order to *relate* Gina to the Company in the mAttachedTo <\/strong>field you would enter a 1 or, since this would be on a Form with a Combo Box to select from, you would select Access Diva (Figure 2).<\/p>\n

\"MemberContacts\"

Figure 2<\/p><\/div>\n

The RowSource for the Combo Box in Figure 2\u00a0is…<\/p>\n

SELECT tblMembers.mMemberID, tblMembers.mCompanyName FROM tblMembers WHERE (((tblMembers.mAttachedTo) Is Null));<\/p>\n

You can either build one Form to enter Members OR you could build a form that has a Main Form for Company’s and Subform for Company Contacts.\u00a0 Using the Main Form\/Subform scenario you could use the mMemberTypeID<\/strong> or the mRegistrationID<\/strong> to identify which Contacts <\/strong>are also Members.\u00a0 To seperate the two is easy… Take a look at the following two queries…<\/p>\n

Companies…<\/strong><\/p>\n

SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
\nFROM tblMembers
\nWHERE (((tblMembers.mAttachedTo) Is Null))<\/b>;<\/p>\n

Contacts\/Individuals…<\/strong><\/p>\n

SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
\nFROM tblMembers
\nWHERE (((tblMembers.mAttachedTo) Is Not Null))<\/b>;<\/p>\n

Download the Sample Database here…\u00a0 Complaints.zip<\/a><\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

You sat down to build your database and things were going great. You made a table for Members and one for Members Contacts. Only you now realize that the Members Contacts can also be Members. At first you think… that will be okay. But then you go to build your forms and realize this is […]<\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[63],"tags":[35,18,40],"class_list":["post-725","post","type-post","status-publish","format-standard","hentry","category-database-design","tag-data-model","tag-database-design","tag-table-design","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=725"}],"version-history":[{"count":9,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/725\/revisions"}],"predecessor-version":[{"id":1278,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/725\/revisions\/1278"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}