Interesting Relationship

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
Difficulty in Modelling an Interesting Relationship
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
How interesting?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,346 Points 50k badge 2x thumb
I was just kidding. If you want one on one assistance to lay out the architecture of your tables and relationships you can contact me at QuickBaseCoach.com
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
We are migrating data from an outdated DB that is no longer supported and moving the data to Quick Base and I have run into an interesting case. The DB has two tables that pertain to this puzzle, Assets and Components. The DB has a page that allows the user to select an asset from the Asset table and link/unlink it to an asset from the Asset table in the Components table.

The Assets table has an asset field while the Components table has a sub-unit field and a unit field (the unit and sub-unit fields are populated from the asset field).

This allowed the asset coordinator to add a group name to the asset field, for example work cell A, and then group additional assets under the asset group name work cell A, for example A1, A2, A3. So work cell A, A1, A2, and A3 are all assets in the Asset table asset field and they are related in the Components table such that when you select work cell A, the children A1, A2, and A3 are displayed.

I have this portion functioning fine, where the puzzle comes in for me is the DB appears to allow a recursive relationship such that a component may have a component. So the relationship appears to be Assets<components<components, for example selecting work cell A displays grouped assets A1, A2, and A3 and then selecting A3 displays grouped assets A3.1, A3.2, and A3.3.


The structure appears in the DB like this:

-Work cell A
          -A1
          -A2
          -A3
               -A3.1
               -A3.2
               -A3.3

All of which are in the Assets table asset field, A1 through A3.3 are in the Components table sub-unit field, and Work cell A through A3 are in the Components table unit field.

Any advise would be greatly appreciated.
Photo of Debbie Taylor

Debbie Taylor

  • 604 Points 500 badge 2x thumb
You can build recursive relationships in QuickBase, too.  That might make sense for you - especially if you are not doing much reporting based on the fields.  You could also create an additional table in QuickBase.  That approach simplifies reporting and data entry; it also makes the database a bit more complex.  Happy to talk.  We are CloudBase Services.
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
I noticed the table was named XREF. I know nothing about cross reference tables in SQL or how they work.