Afternoon everyone,

My first post on sub-types since the thread entitled RE:Boring, which I posted about 2 years ago though it certainly doesn't feel that long.

I've unfortunately been involved with application developers recently who believe that everything where possible should be placed into a single table and all relationships should exist in a single table.

Thankfully I've been able to point out the numerous functional dependency violations that occur with this mindset and also the massive amounts of corruption that occur from a single relationship table. For exmaple, the majority of relationships in the system were stored in a single table with a field that denoted the type of relationship. To make matters worse this table included both 1 to 1 and many to many relationships.

When I pointed this out, the response was that although the table does not enforce correct cardinality for 1:1 relationships, it is acceptable for all m:m tables. From here I highlighted the fact that you still cannot guarantee that entityA will point only to entityB and to this point they said that the data relationships should be checked first. Of course, this reasons is nonsense and was a good enough reason for me to end the discussion.

However, I have now come to a point of mild confusion to which I would be very grateful indeed to receive any advice that any of you generous and knowledgeable chaps may have to offer.

I need to model category information for a collection of products which alone seems to be a simple problem with a correspondingly simple answer. Create a category table and then a relationship between category and product.

The model becomes slightly more complex when we take notice of the associated data rules. Firstly, each category is given a type such as 'International', 'Domestic' and 'Local Specials'. These names are chosen only to assist in illustrating the model requirements. Secondly and unlike hybrid hierarchy implementations, each category can relate to a restricted set of other categories.

As an example, products that are assigned to the 'Local Specials' category may only be related to the 'Domestic' category.

Now as this resembles a hierarchy, the initial approach that one would most likely consider would be to model a single category and products table such as

Products(productname, etc)

Category(CategoryId, CategoryName, ParentCategory)

So how do I enforce the rules?

1) Stored Procedure
2) Foreign Key and Check Constraints
3) Don't enforce them

Option 2 simply involes adding a check constraint to ensure that when the categoryID type is 'Local Specials' then the parentCategory type is 'Domestic', as an example.

The other option is to create a table for each category type and four separate relationship tables. Alternatively, the categories could remain in a single table if desired and just separate the relationships.

A totally different type of option would invole the creation of an 'Allowed assocation table' that would store the foreign key meta data. This could be considered an option if the category type number was foreseen to be large or constantly changing. Initially I do not admire this approach as it has the ring of something an application developer would do.

Basically, it would work as follows:

CategoryType (CategoryTypeID, ParentCategoryTypeID)

Assumptions:

CategoryTypeID of 100 = 'Local Specials'
CategoryTypeID of 200 = 'Domestic'

An example row would be:

{100, 200}

Then, in the category relationship table we could enforce the data rules as follows:

Assumptions:

CategoryID of 1 = 'LocalSpecialsCategoryA'
CategoryID of 2 = 'DomesticCategoryA'

CategoryRelationship
(CategoryID, CategoryTypeID, ParentCategoryID, ParentCategoryTypeID)

Rows in CategoryRelationship:

{1, 100, 2, 200}

Foreign Key (CategoryID, CategoryTypeID) References Category(CategoryID, CategoryTypeID)
Foreign Key (ParentCategoryID, ParentCategoryTypeID) References Category(CategoryID, CategoryTypeID)

-- Enforce the relationship type
Foreign Key (CategoryTypeID, ParentCategoryTypeID) References CategoryType(CategoryTypeID, ParentCategoryTypeID)

I certainly have no intention on having a single such table for all my many to many relationships, not at all.

I'm very much interested in any opinions, criticisms or general comments that you may have.