Post reply

Table Design Question

  • March 20, 2006 at 3:46 pm

    #113333

    Hi,

    I have a design issue that I'd like some feedback on.  I have three tables: tblReligiousInstitutions, tblReligiousAffiliations, tblReligionSubCategories. 

    tblReligiousInstitutions has the following fields: ID, ReligiousInstitution, ReligiousAffliationID (foreign key), ReligionSubCategoryID (foreign key).

    tblReligiousAffiliations has the following fields: ID, ReligiousAffiliationDescription.

    tblReligionSubCategories has the following fields: ID, ReligionSubCategoryDescription, ReligiousAffiliationID (foreign key).

    Some RegligiousAffiliations will have multiple ReligionSubCategories, others have no ReligionSubCategories.

    You can see that the tblReligiousInstitutions has two foreign keys - one for each look-up table.

    I have two options.  First, I can leave the tables as described above. 

    Or second, I could duplicate the tblReligiousAffiliations.ReligiousAffiliationDescription field in a corresponding tblReligionSubCategories.ReligionSubCategoryDescription field. Then, I could eliminate the tblReligiousInstitutions.ReligiousAffliationID foreign key field.  The result would be that I can ALWAYS tell the ReligiousAffiliation by the ReligionSubCategory - even in cirmcustances when an actual ReligionSubCategory doesn't really apply.

    My question is which of these two options is best?  Is one clearly advantageous over the other?  If so, why?  If the answer is six one-way, half-dozen the other, then I won't worry about it.  I just want to make sure that I'm not missing something obvious.

    I appreciate any input you have to offer (please don't hesitate to ask if this description isn't clear).

    Thanks!

  • March 20, 2006 at 3:58 pm

    #627750

    It occurred to me that my question might be a little easier to follow if I provide some sample data.

    Examples of ReligiousAffiliationDescription values would be: Jewish, Christian, Muslim, Buddhist, Hindu.

    Examples of ReligionSubCategoryDescription values would be: reformed, conservative, or orthodox (Jewish), non-denominational or denominational (Christian ), Shiite or Sunni (Muslim).  We have no ReligionSubCategoryDescription for Buddhist or Hindu.

    Hope this helps.

  • March 21, 2006 at 2:46 am

    #627813

    Let's look at it this way...

    - every SubCategory always belongs to some Affiliation, and only to one

    - having both FK's in the table Institutions is not correct, because you duplicate the information and additionally have to place some check that values of these two columns match (that there are no Sunni Jews or similar due to incorrect entry)

    I would suggest entering at least one row for every Affiliation into the table of SubCategories... like "Buddhist - unspecified" or "Hindu Mainstream" or whatever ... and not include the Affiliation FK in table Institutions. In fact, you already have to repeat the religion name in the category description at least sometimes - like with Orthodox, where there are Orthodox Jews and Orthodox Christians (well, in fact maybe the names are different in English, never read much about religions in English so I may be wrong here).

  • March 21, 2006 at 8:05 am

    #627880

    I agree!

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • March 21, 2006 at 4:33 pm

    #627994

    Thanks to both of you.  Very helpful!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply