-5

I would like to know what is better practice (and better performance wise):

Using multiple foreign keys from 1 table to many (so when i fetch the object it's a larger object and contain all other tables information using the relations), or fetching an object from a table and then executing another query to get the rest of the data.

hakre
  • 193,403
  • 52
  • 435
  • 836
Danny Valariola
  • 1,118
  • 5
  • 26
  • 41
  • 3
    It's impossible to answer this. Foreign keys are not a "performance" thing, they are a data integrity thing. You shouldn't be considering performance when designing your database. Build a correct, normalized database, and if performance is a problem, start worrying about how to denormalize it. – user229044 Oct 16 '12 at 13:43
  • 1
    It's not about denormalization; it's basically the question 'Should I use join'? – raina77ow Oct 16 '12 at 13:45
  • @meagar - yes - except that denormalization shouldn't be the first thought for performance. – Randy Oct 16 '12 at 13:45
  • 2
    Misinterpreted the question as, "should I use foreign keys". It's *still* not answerable as the answer will depend *entirely* on specific context. – user229044 Oct 16 '12 at 13:45
  • I'd strongly suggest reading [this thread](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem). In short, it's a well-known _problem_ that some ORM are not capable of using JOINs, using additional queries instead. – raina77ow Oct 16 '12 at 13:47

1 Answers1

2

Usually It depends on how frequently you fetch which columns from multiple tables.

1.If you create many foreign keys in a table and then connect with these keys to many others then you will end up joining many tables to fetch required data.

2.So I suggest you first decide what type of data you are mainly looking from this main table and how frequently you use it.

3.Also you need to create indexes on all joining keys in order to achieve better performance.This is in one way overhead and in other way saves you time.

So it all depends on your needs.

AnandPhadke
  • 13,160
  • 5
  • 26
  • 33
  • Thanks, i think what answered my question was: "first decide what type of data you are mainly looking from this main table and how frequently you use it"... :) – Danny Valariola Oct 16 '12 at 13:50