Results 1 to 3 of 3

Thread: Derive relationships based on data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Derive relationships based on data

    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.

    Thank you

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    450
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Derive relationships based on data

    If the foreign key and primary key have the same name you can use a self join on the table, see here:

    http://allenbrowne.com/ser-06.html

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Derive relationships based on data

    Quote Originally Posted by stumac View Post
    If the foreign key and primary key have the same name you can use a self join on the table, see here:

    http://allenbrowne.com/ser-06.html

    This works if you are looking for table relationships. I am looking for a way to determine relationships from the date.

    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


    Not sure if this makes sense

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •