I am attempting to design a credit card applicatoin to keep academic reference information. The issue is that every different kind of reference (eg. journal articles, books, newspaper articles etc) requires different information. For instance a journal reference requires both a journal title as well as an article title, in addition to a page number, whereas a magazine takes a writer along with a publication date which journal articles don't require.
Therefore, must i have the references saved in a single table during my database and merely leave fields blank once they don't apply, or must i have various tables for example BookReferences, JournalReferences, NewspaperReferences and set the right references in each one of these. The issue then is it might make searching through all of the references more difficult, as well as editing would need to be achieved more individually most likely.
(I am likely to use Ruby on Rails with this project incidentally, however i doubt which makes any impact on this design question)
Anymore sights about this? I wished to obtain a simple answer stating that a specific method was certainly considered 'the best' - but as always things aren't quite as easy as this. The Only-Table Inheritance option looks very worthwhile, but there is not many details onto it will be able to find effortlessly - I might publish another question on this website about this.
I am split between Olvak's answer and Corey's answer. Corey's answer gives a very good reason why Olvak's is not the very best, but Olvak's answer gives reasons why Corey's is not the very best! I never realized this may be so desperately...
Any more advice much appreciated!
I'd choose getting just one table for those references, but additional tables like BookReferences and so forth for metadata not relevant for those reference types.
Searching and querying wouldn't be harder - in the end you can just produce a view which aggregates information as with the only-table solution, after which query that view further.
Getting my way through one table with a lot of nulls might appear such as the simpler solution, but really it'll result in plenty of trouble. For instance: With separate tables you are able to define which fields are needed for each BookReference, but when things are in a single table, every area needs to be nullable and for that reason optional. It might be also simpler to place invalid data, just like a book reference that also erroneously consists of a non-null journal title.
Edit: Many people appear to fear joins. Don't fear the join! If you are using the identical participate in several queries that will indeed be tiresome, but for the reason that situation the join ought to be defined inside a view, and also you queries should query that view. Sights are actually the fundamental abstraction in relational databases, and you ought to utilize them for exactly the same reasons you utilize functions in code: to prevent repetition, and also to encapsulate and make abstractions.
Edit: You will find some comments regarding performance. It's very difficult to guess in advance about performance of DB schemas, since it is frequently non-intuitive. For instance a join between several tables may be easily faster than the usual full table scan of merely one table - everything is dependent on the kind of query, the character from the data, the accessible indexes and so forth. Furthermore, in lots of database systems you should use features like materialized sights to optimize performance for various queries without compromising the logical model. "Denormalization for performance" is mainly cargo cult nowadays IMHO, unless of course you're Google or Flickr.
"existence is simpler using the one large table": I have seen natural results of this, as being a 100+ column table, and I will tell you I've found this no pleasure to utilize. The primary problem would be that the designers of these tables often omit the constraints needed to make sure data integrity:
CHECK (NOT (type <> 'book' AND area_common_to_book_and_journal Isn't NULL))
and so forth.
It's my hope that whenever writing hundreds of such constraints the designer might have second ideas about all individuals nullable posts :)