We are trying to model a school system hierarchy from State>County>District>School, hence we are using the model below to describe the parent child relationship along with organization type (i.e. School, District,…).
Now we need to store details on each particular organization, for example if it’s a school we need to store the more general data that each organization has such as address, city and zip code, but we also need to store the more specific organizational data such as the grades been taught at a school. Hence we are not able to create a generalize organization details table since a district does not teach any grade levels and other differences between organizations. What I have come up with is having a separate table for each organization type, (i.e. school details table, District details table and so on) and then creating foreign keys from the details table to the Organizations table. My question is, is this good design or is there something better? Also how would I be able to map the tables in Nhibernate such that I am able to do something like the following:
organization.addDetails(schoolInfo) or organization.getDetails()
Here is the mapping that I have for the hierarchy table, I haven’t created the rest since I’m not sure that we are using the best design to store organization details.
<class name="Organization" table="[Organization]" lazy="true">
<id name="Id" access="property" column="Id" type="Int32" unsaved-value="0">
<generator class="native" />
</id>
<property name="Name" access="property" type="String" not-null="true" />
<many-to-one name="Parent" class="Organization" column="ParentId" not-null="false" />
<set name="Children" cascade="save-update">
<key column="ParentId" foreign-key="fk_Organization_Parent_ParentOrganization"/>
<one-to-many class="Organization"/>
</set>