none
Design basics

    Question

  • Hi experts, this is a design question…

     

    I am the DBA for a new environment it is for Ecommerce, they use salesforce and they have data for members, leads, etc… This data is in sql server too, they feed it from Salesforce hourly….

     

    I am not a design expert, but I have seen many reports, SPs there, and I have seen many tables joined by “email”…  I see lots and lots of LEFT JOIN…. When I ask questions to Ecommerce devs,  they say they built it that way because different markets have different business rules…

     

     

    My questions are:

    1-      Is it acceptable to join by email?

    2-      Is it normal to see more LEFT JOINs than INNER JOINs?

    3-      If a new business rule, breaks all my design/table, is it acceptable to “join by email” as a solution, or the use of left join?

    4-      I haven’t seen any constraints or FK in ANY table…

     

     

    What can I do as a DBA to help this situation/ whose responsibility would be to start creating FK and come up with coherent designs…

     

    Thanks!

    Ps: It good be helpful if anyone can recommend any bibliography that discusses this.

    Friday, April 21, 2017 1:45 PM

All replies

  • I surprised on this post from DBA. 

    • Email is always unique and that could be a simple reason why it was taken as key.
    • Coming to LEFT JOIN vs INNER JOIN ----both do not serve same purpose. So, whoever wrote SPs would have known that data in right side table of JOIN is optional.
    • Your 3rd question is combination of 1st and 2nd , if you could figure out 1 & 2 then 3 is straight forward.
    • Don't see FKs ? well, that could be data ware house. Is the database you are referring is warehouse or reporting db?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 21, 2017 3:47 PM
  • Sarat, I do not think your reply solves my issue.

     

    Email, may not be unique… Furthermore I do know it’s a bad practice, joining by an INT is more performant.

     

    Left joins are not as performant as inner join, abuse of Left joins, may be an indication of wrong design…

     

    Datawarehouses may or may not have FK.

    Friday, April 21, 2017 4:21 PM
  • Sarat, I do not think your reply solves my issue.

     

    Email, may not be unique… Furthermore I do know it’s a bad practice, joining by an INT is more performant.

     

    Left joins are not as performant as inner join, abuse of Left joins, may be an indication of wrong design…

     

    Datawarehouses may or may not have FK.

    I see that you marked my reply as abusive.

    I cannot resolve your issue , because your questions are too generic, no information on tables or data.

    I defer with your statements.

    1. Email will always be unique , but many organizations chose INT keys to have better performance. When you talk about eCommerce application which might be dealing with multiple accounts on different websites, they would have chosen EMAIL as key.
    2. LEFT and INNER join serve different purpose ( if you think that INNER JOIN is better than LEFT JOIN, sorry but you have to do more research on this, thats not true)
    3. Data warehouses (generally) have de-normalized tables so that too many joins can be avoided. 

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • Edited by Sarat (SS) Friday, April 21, 2017 5:41 PM
    Friday, April 21, 2017 5:34 PM
  • Email is not a good field to use to join on as it tends to be wide. There should be an email table with an email ID value which is an int data type. This would be far more efficient to join on.

    Email addresses should be unique, but do not have to be. For one client I have they have found multiple children sharing their same parent's email address. It depends on the business use. For ecommerce I would suspect it should be unique.

    Combinations of left and inner joins are very common.

    Constraints are a best practice. Not having any raises many red flags.

    Friday, April 21, 2017 5:43 PM