locked
How to create an ERD for table does not have proper Primary or Foreign Key RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi There

    I have to create an Entity Relationship Diagram for previous created database. But there are no Primary key or foreign key defined in the tables. Most of the tables have auto increment number as a primary key so I am struggling how I am going to define a relationship to create an ERD. Can someone pleeeese kindly help me with that.

    There are also some reference data tables and staging tables in the database. When we are drawing an ERD do we have to include all the tables in the database? Sorry I am quite new to the industry your assistance on this would be very much appreciated.

    Many thanks

    Tuesday, May 19, 2020 12:27 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    No PK/FK = No Entity Relationship; so what do you want to draw here?

    May be there are some relations "by meaning", but this you have to find out (e.g. reviewing views, SP and other SQL code), we even can't guess.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, May 19, 2020 12:30 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    There are "physical" and "logical" ERD diagrams.  You don't need PK and FK enforcement at the database layer to describe the relationships in an ERD.

    If they don't exist in the database, you will need to link them manually.


    Tuesday, May 19, 2020 12:35 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Appreciate your quick respond Tom, I've studied all the tables and there is a field I can use for connecting tables but it does not a primary key.  When we do link them manually do we still have to consider relationship type like one to one, one to many so on?

    In the database consist of some main tables, staging tables  & data reference tables so do we have to include all of them to our ERD please?

    Thank you so much for your help…

    Tuesday, May 19, 2020 12:44 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Once you are crafting an ERD, you will have to include all the tables in the database.

    There is no other way to do it.

    To create relationships, you will have to link them manually.

    Tuesday, May 19, 2020 12:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Appreciate your quick respond Olaf, yes there is a field which is available in most of all tables so do you think probably I can use that to make relationship among tables? Thanks

    Tuesday, May 19, 2020 12:47 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Rhodes, when we guess the relationship based on the table do we still have to mention the relationship types such as one to many or one to many so on?

    Thanks heaps

    Tuesday, May 19, 2020 1:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Appreciate your quick respond Tom, I've studied all the tables and there is a field I can use for connecting tables but it does not a primary key.  When we do link them manually do we still have to consider relationship type like one to one, one to many so on?

    It depends on your purpose. Are you simply trying to get a graphic representation of your tables and relationships? Tables can have a logical PK without a PK enforced at the database level

    In the database consist of some main tables, staging tables  & data reference tables so do we have to include all of them to our ERD please?



    Again it depends on what exactly you are trying to represent.

    Tuesday, May 19, 2020 4:12 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    Once you are crafting an ERD, you will have to include all the tables in the database.

    There is no other way to do it.

    Eh why? Given that this database seems to be in a less than ideal state, I can imagine that there are a lot of junk tables that can be dropped.

    I don't envy Saphire77. It sounds like a real dirt job. To do it well, you will need to dig deep amoung those table to understand them. And talk to a lot of people to find the meaning of the tables and columns. If you are new in this business, I think it is quite unfair to give this task to you.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 19, 2020 9:46 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Saphire77,

    Suggest post in Forum_tsql

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 20, 2020 8:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Suggest post in Forum_tsql <https://social.msdn.microsoft.com/Forums/en-US/home?forum=transactsql>

    Why? Saphire77's question is definitely related to database design.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 20, 2020 9:35 PM