locked
Are recurring foreign keys big sin for normalization RRS feed

  • Question

  • User283528319 posted

    Hi all

    lets say we have 3 table

    customers, orders, items

    is it a big mistake to add customersId field to items table while we can get that id from orders table (if we join that table using orders Id foreignkey)

    I meant is it wrong to make an item table like this;

    Id

    Item

    odersId

    customersId

    Wednesday, August 14, 2019 1:33 PM

All replies

  • User-821857111 posted

    Yes. 

    Wednesday, August 14, 2019 2:12 PM
  • User283528319 posted

    what about performance and speed.

     I meant is there any chance getting data from this table using custemerId directly is much faster than getting this information from joined orders table?

    what do you think?

    Wednesday, August 14, 2019 2:15 PM
  • User-821857111 posted

    No. I don't think there is much chance of a significant difference in speed of execution. If there was, all the professional advice would be to include extra foreign keys in tables in case you wan to join them directly. I have never seen any advice like that, so I have no reason to believe that there is any advantage at all in this kind of approach.

    The only time I have ever seen anything like this was in a database that was very badly designed to begin with. It's a workaround at best, not a solution. 

    Wednesday, August 14, 2019 2:25 PM
  • User283528319 posted

    thanks

    Wednesday, August 14, 2019 8:01 PM
  • User283528319 posted

    An interesting thing happened.

    When I get the second key from joined table the query time increased 5 times. I am afraid My worries were true... :(

    join really slowdowns query time.

    Friday, August 16, 2019 7:01 AM
  • User753101303 posted

    Hi,

    Do you have proper indexes in place ? Are you sure it produces the same row count? The difference seems really huge and I suspect just short circuiting joins is not the only difference you have.

    Start maybe with a SELECT COUNT(*) FROM etc... to really just test your FROM/JOIN clauses and check you return the same row count.

    Friday, August 16, 2019 11:34 AM
  • User283528319 posted

    I really thought the same and checked indexes, indexes look fine...

    Friday, August 16, 2019 11:35 AM
  • User475983607 posted

    Is there any way you can provide sample DDL and DML that reproduces this issue?   Also can you quantify what 5 times equates to?  The query went from 100ms to 500ms?    

    Friday, August 16, 2019 11:47 AM
  • User753101303 posted

    5 times is useless without actual figures (I saw once someone suggesting you should avoid method calls because he found that coding without any method call is x time quicker than a code with method calls - and quite obviously doing nothing is much quicker than doing something - he just forgot this "huge" difference is not visible to a human). I let you imagine which benefits/problems ratio you would have if trying to write your app with as less method calls as you can.

    You are testing with SELECT * ? In this case you may see a difference which is mostly spent in showing column values (as you'll have columns for 2 tables rather than for all involved tables). T his is why I suggested to start with a COUNT(*) query which shows the same result regardless of how you write your query.

    If it doesn't help I should have a db with 5 or 6 level of parent/child relation that I could test to see which difference it makes for my own case….

    Friday, August 16, 2019 11:50 AM
  • User283528319 posted

    the difference is from less than 1 second to more than 5 seconds. It is visible with naked human eye.

    Friday, August 16, 2019 12:56 PM
  • User475983607 posted

    fatihbarut

    the difference is from less than 1 second to more than 5 seconds. It is visible with naked human eye.

    The symptom points to a DDL, DML, or design bug.  Share code that reproduces this issue if you need community debugging support.  Keep in mind that many developer on this forum support DBs with millions of records and are not having this issue.  

    Once I had a developer complain of the same type of issue only find a joined a table with embedded word documents.  The dev was using SELECT * which clearly returns all the documents which takes time to send over the wire.  

    Friday, August 16, 2019 1:12 PM
  • User753101303 posted

    Any feedback about previous suggestion such as using SELECT COUNT(*) to make sure the issue is not spending time in fetching/showing additional columns rather than I actually doing additional joins ?

    Saturday, August 17, 2019 10:31 PM