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.
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"?
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).