2

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

3
  • 1
    Not sure if I understand your problem, but it seems rather trivial: Just use an auxiliary table enumerating the 6 (or whatever) different types of contexts: find -> context -> contextType
    – jap1968
    Mar 26, 2013 at 23:20
  • @jap1968: how to address the "contextType" tables from the "context" table? Should they all have a common primary key (I must assure by triggers that the key values are unique within the sum of "contextType" tables, right?)? If there's no common key, how would the "context" table know which "contextType" to address? And how to implement such a switch? By columns with foreign keys for each and a trigger assuring that exactly one of them is not null?
    – Pavel
    Mar 27, 2013 at 10:08
  • 1
    "But how to assure that exactly one row in all the dependent tables corresponds to each row in context?" By having the contextType column as part of the primary key (and the foreign keys). Mar 29, 2013 at 1:24

1 Answer 1

1

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:

  1. The context records have foreign keys that reference other tables depending on subtype, and

  2. 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.

1
  • This is quite close to what I actually did (I used single key common for context and did some trigger work to make it safe - composite foreign key wasn't very handy because the context's primary key was referred by many other tables). Your answer came too late to really help me, but it is a good answer. Accepted and +1.
    – Pavel V.
    Sep 13, 2013 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.