Inheritance and its alternatives in PostgreSQL I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it:

1) to violate third normal form

It is the dirtiest and probably the worst solution. I could check potencial duplicities by triggers, but I suspect it would be the hardest way, for there are quite a lot of type specific columns.

2) inheritance

This is better and might be good enough, but my "finds" table should have a foreign key to the "context" table regardles of its type. According to the manual this would cause problems. With all data in tables "layer", "cut", "structure" etc., would the foreign key to "context" table really point to the data stored in the inherited tables? If there's any caveat, is there some (relatively) easy workaround?

3) some sort of 1:1 relation

The "finds" table would be connected to "context" table, and tables for various types of context would have a foreign key pointing to the "context" table. But how to assure that exactly one row in all the dependent tables corresponds to each row in "context"?

4) ???

There might be some other solution which I didn't know about.

Which of these is easiest to implement (including avoiding duplicity)? And which is best in the terms of performance?
The database will never be really big, so slightly suboptimal performance is no problem, but big differences in performance might be more important than easiness to write it.

I'd be glad for answers telling me which option I should use, why, and especially: how to overcome its drawbacks (or that I missed something and there's no real drawback).
It is hard to answer the question as written.  The simplest solution would probably be something like a context table with types listed as they are.  What I am not sure about is what you need in terms of linking to this table.  In other words, have all the common fields for context in the context table plus a type field that tells you what kind of context it is.  Other tables can then join that to provide more information about the types.

The problems you mention with the 1:1 design can be solved with composite foreign and primary keys.  It may seem like field duplication but keep in mind your primary key is multi-field so your foreign keys would need to be also.  This allows you to ensure that different dependent tables do not overlap where you do not want them to do so.

The second solution is to use inheritance.  In my view, inheritance is going to be a good solution if the following are true:


The context records have foreign keys that reference other tables depending on subtype, and
No tables reference context as such but rather the subtype directly.


My guess is that in your case the second condition will not be met and therefore I would not recommend table inheritance for you.