none
Derive relationships via data values RRS feed

  • Question

  • Hi there, 

    Hi all, 

    I have a database - about databases - so what it does is stores all information about the various databases that we run, table names, columns that are linked column sizes, descriptions etc. 

    I have a System table that links to a MasterTable (table) and then a column table. The column table stores a foreign key and a primary key indicator. 

    Is there a way to derive relationships by reading through the MasterTable, picking up the Primary key indicator from the Column table, and then checking for foreign keys in other columns that belong to the same system. 

    I have code that can determine relationships between actual tables in MS Access, but I need to determine the relationships based on the data that is stored. 

    For example

    SystemTable_data
    SystemA
    SystemB 
    SystemC

    MasterTable_data
    SystemA Table 1 
    SystemA Table 2
    SystemA Table 3
    SystemB Table 4


    Column_data
    Table 1 Column A PK
    Table 1 Column A FK
    Table 1 Column A 
    Table 1 Column A 

    Table 2 Column A PK
    Table 2 Column A 
    Table 2 Column A 
    Table 2 Column A 


    Table_results - this is the table that I would need to generate 
    System Table RelationshipType
    SystemA Table 1 Child to Table 2
    SystemA Table 2 Parent to Table 1

    Friday, July 26, 2019 5:44 AM

All replies

  • Hello,

    What have you tried so far?

    Do you have primary keys setup to relate to each table e.g. if you traverse down or up you can find child objects downward or find parent objects moving upwards? Having foreign keys would be the path to learning about your data.

    On a side note, I would have looked into a ORM such as NHibernate which may or may not work for you yet might be worth exploring.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, July 26, 2019 11:16 AM
    Moderator