A supertype table without (identifying) unique properties

Question

In an application that I develop a situation occurs every now and then where the SuperType/SubType pattern is needed. My problem usually appears when an exclusive specialization of sub types applies. The specialization's exclusiveness itself can be easily enforced in the programming language layer, so making use of the pattern is a trivial task. However, sometimes the super type does not have identifying properties (columns) from itself - other than an id, which is present in every table that I create. The actual identification is specified in every sub type table. By identification, I mean a combination of columns with unique values, like:

• In a countries table a name
• In a provinces table a country_id + name
• In a car_wheels table a car_id + wheel_position

I learned that virtually every table somehow contains identifying columns.

In my particular case I have the following tables:

documents
-----------------------------
id  |   publicly_accessible

webpage_documents
----------------------------------------------------------
id  |   document_id |   name (as well as certain other properties, too)

ajax_script_documents
-----------------------------
id  |   document_id |   name

pdf_documents
-----------------------------

etc...

There exist relation tables which reference documents and each document's sub type's potentially got a whole sub system for its own. Hence the SuperType/SubType pattern.

As you can see, the documents table does contain a boolean column publicly_accessible, which is obviously not a identifying column. There are also situations where even such a column is absent. That means the super type table only contains a id column, which to me not only smells but is also confusing when viewing its contents.

I can't just remove the documents super type, because the number of sub type tables may grow over time and there are several tables explicitly referencing documents, which would mean that the number of tables referencing documents would have to be multiplied with the number of sub types.

So basically I'm short of identifying columns for my super type table. Any ideas how to redesign/come up with a useful identifying column?

Answer 1

Hi I'll add another answer as this will be to much for a comment and I do not want to take away the first one.

Well, I think you mix up some concepts:

Your example with countries, provinces or car_wheels are "nested components". Mostly they are related 1:n (1 country, many provinces, 1 car, many [well, probably 4] wheels). Sometimes there is the special situation of 1:1 (1 car has 1 engine), but this is not by definition but by rule. You could think of a car with two engines (e.g. otto and battery). This is NOT inheritance (super type [base class], type and sub classes). The nested type will not fit to the "is a" rule. A province is not a country, a wheel is not a car...

Inheritance is always 1:1. The sub type (in your case a special document) is a document.

In such a scenario you have tree kinds of data:

1. Common data (in your case this would be the docID, a name, author and status information and a docType (either as a FK to a side table or stand-alone)
2. Overrideable data: The super type or one of its side table defines a default and the special doc row may overrule this value (in your case probably something about rights, accessibility...)
3. Specialised data: Data which belongs to the specialised type but not to all document types

Normally you would use VIEWS for each specialised doc type to deliver the compound data of the super type and the derived one to behave as if it was a table. With SQL Server you can define such VIEWS with schema binding, with indexes and constraints.

From your question it was not totally clear to me, that you do not want to change the basic concept. No problem, stick to your doc-table, but think about the following: Do you really need specialised tables for the different doc types? Your only example states in all cases "name", which is quite a common information which should rather be in the super table. What is the benefit? What information is so unique for a PDF, a DOC, a HTML, that several different tables are necessary?

In our biggest project we have just one document table for all types of docs and I could really think of no advantage to go away form there... There are some columns which are nullable and are not filled in any case, thats absolutely sufficient...

Good luck in finding the best and fitting concept!

Answer 2

I'd think about a DocumentType Table. This new table knows the type's name and additional information (general storage place, application to view, ...)

You could either let your super table away and place this new id (with FK) into the spread doc-tables, or you keep your super table and place it there...

Wether a doc is publicly accessible may be a value on doc-type level or a value on doc-level. I prefer a value cascade in such scenarios: There could be your boolean column with NOT NULL as a part of the doc-type table and another nullable column on doc level (either in super table or in spread table). You take this value if NOT NULL and fall back to the default set in your doc-type...

The super table is only usefull, if you use common information such as author, creation time, status, last edit, what ever...