locked
Basics using the diagram designer in SSMS RRS feed

  • Question

  • H, we run 2012 std.  I'm playing with the diagram designer and looking up my questions on the web but 3 things escape me (so far). 

    One, is there a way for a specific fk relationship to point right at the cols involved vs from/to the title bars of each table?  Perhaps with the fk name/description showing also?

    Two, the web suggests one to many and many to one relationships can be diagramed but I don't see how.  All I see is what looks like a one to one without the familiar fan or figure 8 on the endpoints.  No junction records involved in this question.

    Third, the web suggests u can select multiple fields from the table that will have a relationship with another table before dragging but I tried shift, alt, ctrl on the 1st table's columns but don't seem able to choose multiple non adjacent cols for dragging.  I do see a relationships icon that appears to enable when a column is selected but it always seems to add the relationship connector randomly to the "from" table itself.   





    • Edited by db042190 Wednesday, December 3, 2014 2:49 PM better title
    • Edited by Kalman Toth Thursday, December 11, 2014 11:00 AM Spelling
    Wednesday, December 3, 2014 2:09 PM

Answers

  • Hello,

    Q1: No. Not possible to make the relationships lines of the database diagram to point to the fields involved on the relationships.



    Q2: The yellow key means 1 and the 8 represents the many.



    Q3. Right click on the tables involved in the database diagrams, and choose “Relationships”, click on the Add button, then click on the ellipsis button associated with “Tables and columns specific”..



    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Wednesday, December 3, 2014 3:39 PM
    Answerer
  • Hello

    Many-to-many relationships need to be transformed (refined) in the end into two one-to-many relationships, in order to implement them on a relational database.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Tuesday, December 9, 2014 7:17 PM
    Answerer
  • I think the distinction you need is that the Diagrammer is a PHYSICAL model and not a logical model .. therefore you cannot implement any logical modeling techniques - this is because when you create tables. columns, keys, relationships etc these are always physically implemented in the database at the same time.
    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Tuesday, December 9, 2014 11:46 PM

All replies

  • Hello,

    Q1: No. Not possible to make the relationships lines of the database diagram to point to the fields involved on the relationships.



    Q2: The yellow key means 1 and the 8 represents the many.



    Q3. Right click on the tables involved in the database diagrams, and choose “Relationships”, click on the Add button, then click on the ellipsis button associated with “Tables and columns specific”..



    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Wednesday, December 3, 2014 3:39 PM
    Answerer
  • thx Alberto. 

    re Answer 2...so u must be saying sql decides based on source col whether this is many to one or one to one?

    re Answer 3...when I use the ctrl key to select two tables, and rt click, relationships r greyed out.  When u pick relationships on rt clicking one table alone, sql seems strict in requiring that the relationship u r building is either one or many to one.  It will not allow the source table to have a relationship to non unique key(s) in the target table.  I hope u understand what I am getting at.  U may question the design but I still need to do this.

    Wednesday, December 3, 2014 3:52 PM
  • It will not allow the source table to have a relationship to non unique key(s) in the target table.  I hope u understand what I am getting at.  U may question the design but I still need to do this.

    What is a non unique key? That is not a key. A key has to be unique.

    FOREIGN KEY REFERENCE (to another table) can be created to:

    PRIMARY KEY

    UNIQUE KEY

    UNIQUE index




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Tuesday, December 9, 2014 3:18 PM
  • u r right Kalman bad choice semantically.  Should really have called it "the columns used in a non unique index".  I was hoping to use this product for modeling more than just fk relationships.
    Tuesday, December 9, 2014 3:55 PM
  • Did you mean one to many relationship?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, December 9, 2014 4:10 PM
  • I think it was many to many without junction record.  I have to go back to c where I gave up.

    Tuesday, December 9, 2014 5:36 PM
  • Hello

    Many-to-many relationships need to be transformed (refined) in the end into two one-to-many relationships, in order to implement them on a relational database.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Tuesday, December 9, 2014 7:17 PM
    Answerer
  • I still have to go back to my notes, I've forgotten some of the details by now.

    But I'm reading between the lines a little and it sounds like u r saying this tool isn't also useful for logical etc type modeling. 

    Also, I think physically u could be a bad dba and make a many to many without a junction but u r saying that this tool has no provision for graphically showing that physical implementation. 

    Tuesday, December 9, 2014 8:20 PM
  • Hello,


    On the physical implementation, many-to-many relationships should be represented as 1-many relationships.

     “Many-to-many relationships cannot be used in the data model because they cannot be represented by the relational model.”

    Source:  http://academy.delmar.edu/Courses/ITSW1407/RefiningER.html



    Hope this helps.



    Regards,



    Alberto Morillo
    SQLCoffee.com

    Tuesday, December 9, 2014 9:10 PM
    Answerer
  • I think the distinction you need is that the Diagrammer is a PHYSICAL model and not a logical model .. therefore you cannot implement any logical modeling techniques - this is because when you create tables. columns, keys, relationships etc these are always physically implemented in the database at the same time.
    • Marked as answer by db042190 Wednesday, December 10, 2014 11:16 AM
    Tuesday, December 9, 2014 11:46 PM

  • On the physical implementation, many-to-many relationships should be represented as 1-many relationships.

    Hi Alberto,

    What does that mean?  Thanks.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Thursday, December 11, 2014 11:02 AM