Post reply

Data relationship mapping without having declared Foreign Keys

  • March 2, 2010 at 3:41 pm

    #98998

    So I have the task of mapping out the data relations in my company's numerous databases. How the date relates in each database and between.

    It looks like a daunting task since the use of declaring foreign keys as foreign keys was not done for the most part.

    I am believing that the only good way to go about this is to evaluate the joins in the numerous stored procedures to identify the relationships.

    While I identify the relationships I need to know the cardinality (1 to M, M to 1, 1 to 1, etc) in the relationships. There is a lot of tables and understanding what is stored in each for certain use is also a daunting task in itself. So understanding the context of each relationship (and we are talking hundreds) will be slow.

    My question is does anyone know of a script or advice for evaluating this massive about of joins to get this information (including the cardinality)?

    Any advice is welcome.

    Thank you in advance.

  • March 2, 2010 at 6:52 pm

    #1127487

    tfader is there any common themes applied to the parent-child fields, even when no FK is created?

    for example, if the following are all true, i have a script i call "find missing and implied foreign keys"

    which can help.

    • the first column of a table is the PK/identity of the table.
    • a foreign key column is always the same name as the PK column it points to, similarly, as an FK, it would never be the first column of any table
    • pk columns end in "ID" or "TBLKEY" or some common naming convention so it's clear it is a PK/FK.

    once the FK's are in place(just on a dev db) you can use that metadata to find all FK's that have one to many data in the db.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply