Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 '13 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 '13 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). –  ypercube Mar 29 '13 at 1:24
add comment

migrated from stackoverflow.com Mar 29 '13 at 0:20

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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 Sep 13 '13 at 11:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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