locked
erd null doubt RRS feed

  • Question

  • Hi experts,

     

    I wanted to document some of the tables on a kinda ‘legacy’ database, there is 0 documentation and many users keep asking the same questions about the tables. (How do I join x and y…). It was built years ago, a few users use still use it…

     

    Not sure if as DBA is 100% my responsibility but I wanted to give it a shot.

     

    The first problem I come across is, I know little about the data, and most tables don’t have FK… but I have access to most commons SPs/views executed, and I do see lots of joins… Problem is, joins are not done on primary keys…

     

    I see for example A join B on A.col1 = B.col1   and col1 is not a PK on neither A nor B

     

    -          is it ok to assume when there is a join in a table, to build the relationship as fk

    -          how do I know if it’s many to many or one to many or many to one? is it ok to manually do a count(*), group by?

    -          what if there are NULLs on one of the joining keys? is there a way to write that down in the diagram?

    Monday, February 26, 2018 6:55 PM

Answers

  • >> is it ok to assume when there is a join in a table, to build the relationship as fk<<

    Wow, that is a terrible design you are dealing with. You can only model a FK relationship on a PK, or a UNIQUE constraint. It is certainly questionable if (in your example) col1 in A or B meets that definition, and just because you join on a column, doesn't mean either is a key.

    >>how do I know if it’s many to many or one to many or many to one? is it ok to manually do a count(*), group by?<<

    I would suggest doing a bit of data profiling of the data in col1 in both tables to see if either column constitutes a key or not. (and ideally, if one (or both) does, make it a constraint based key so the data always fits. So yes, definitely try a count(*) on the data to look for places where there are > no duplicates

    >>what if there are NULLs on one of the joining keys? is there a way to write that down in the diagram?<<

    Every data modeling language will have some way to indicate that a relationship is optional or mandatory. In IDEF1X, there will be an empty diamond. In the crow's feet method it will be a hash mark on the line on either end. 

    You will be able to read the relationship as either One-Many, or Zero or One To Zero To Many, indicating that a parent is required in the relationship (meaning a NON NULL FK value), or that a child is required (you can have a One To At Least One or More cardinality for example, meaning for a parent row to exist, a child must exist. This is is not enforcable in the database constraints, but it is something that does exist in the customer's requirements, such as a customer row should not exist unless at least one sales order for that customer exists)

    Hope this helps a bit. Trying to interpret a database with no documentation and no keys is a beast.



    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Wednesday, February 28, 2018 3:12 AM

All replies

  • Hi maca128,

    Could you please share more information related to this question? Please share some example or data to us for analysis.

    Besides, did you want to create foreign key based on the current SPs/views?

    Best Regards,

    Teige


    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.

    Tuesday, February 27, 2018 7:58 AM
  • >> is it ok to assume when there is a join in a table, to build the relationship as fk<<

    Wow, that is a terrible design you are dealing with. You can only model a FK relationship on a PK, or a UNIQUE constraint. It is certainly questionable if (in your example) col1 in A or B meets that definition, and just because you join on a column, doesn't mean either is a key.

    >>how do I know if it’s many to many or one to many or many to one? is it ok to manually do a count(*), group by?<<

    I would suggest doing a bit of data profiling of the data in col1 in both tables to see if either column constitutes a key or not. (and ideally, if one (or both) does, make it a constraint based key so the data always fits. So yes, definitely try a count(*) on the data to look for places where there are > no duplicates

    >>what if there are NULLs on one of the joining keys? is there a way to write that down in the diagram?<<

    Every data modeling language will have some way to indicate that a relationship is optional or mandatory. In IDEF1X, there will be an empty diamond. In the crow's feet method it will be a hash mark on the line on either end. 

    You will be able to read the relationship as either One-Many, or Zero or One To Zero To Many, indicating that a parent is required in the relationship (meaning a NON NULL FK value), or that a child is required (you can have a One To At Least One or More cardinality for example, meaning for a parent row to exist, a child must exist. This is is not enforcable in the database constraints, but it is something that does exist in the customer's requirements, such as a customer row should not exist unless at least one sales order for that customer exists)

    Hope this helps a bit. Trying to interpret a database with no documentation and no keys is a beast.



    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Wednesday, February 28, 2018 3:12 AM