Email sent to Wayne on 2/10/24 - dealing with sub inventory attributes and parent attributes

Wayne,
Good morning! I hope all is well.

Here is some basic information about subs and parent attributes. It's kinda techy, at the database level.

- Parent attributes are the easiest to understand...
    - They start in a table called parent_attributes. The key id is called parent_attribute_id
    - If it is used, say for custom_text, it will have an entry like:
        - custom_table_id = autonumber
        - corp_id = the corp id
        - parent_table = 'parent_attributes' (string)
        - parent_column = 'parent_attribute_id' (string)
        - parent_id = foreign key from the parent_attributes table >> parent_attribute_id
        - column_number = 0
        - data_value = 'the value of the parent attribute - open entry' (whatever - this is the actual value)
        - main_id = foreign key from the parts table (part_id)
        - app_type_id = foreign key from the app_types table (app_type_id) - 11=parts
        - sub_id = technically, it is a foreign key, same as the parent_id field. I would use the parent id field if I were you.

- sub attributes are harder to understand... poor design, in my mind.
    - They start in a table called sub_inventory_attributes. The key id is called sub_inventory_attribute_id (for the record, this id is not used in the other table - kinda weak design).
     - If it is used, say for custom_text, it will have an entry like:
        - custom_table_id = autonumber
        - corp_id = the corp id
        - parent_table = 'time_sub_inventory_53' (where 53 is a variable for the corp id) (string)
        - parent_column = 'sub_reference_id' (string)
        - parent_id = foreign key from the time_sub_inventory_53 table >> sub_reference_id (aka the sub id)
        - column_number = some number (super important... this column number comes from the sub_inventory_attributes table assignment) - once again, somewhat poor design. This is the only tie-in to the sub_inventory_attributes table.
        - data_value = 'the value of the sub attribute - open entry' (whatever - this is the actual value)
        - main_id = 0 (somewhat wasted field - currently just stores a 0 for subs)
        - app_type_id = 1 >> foreign key from the app_types table (app_type_id) - 1=dummy (wasted field for subs)
        - sub_id = 0 (somewhat wasted field - currently just stores a 0 for subs)

I can't tell you how much I wish that we could redo the sub attributes. It would be awesome if we were able to catch the sub_inventory_attribute_id, the part_id, and the part_category_id. It would make it so much easier to figure out. Anyways, hopefully that helps. Thanks Wayne!

p.s. If I could change things... it would look something like this... (this doesn't exist yet)
- Inside of the custom_text table...
    - parent_id = foreign key back to the sub_inventory_attribute_id
        - take the existing parent_id value and store it over in the sub_id column. This really is the sub id or sub_reference_id
    - main_id = foreign key back to the parts table (part_id) of the sub
    - app_type_id = foreign key back to the app_types table (11=parts).
    - I would also add a new column called part_category_id or something and store the part_category_id. This is a huge part of how the subs are tied in.

Anyways, just some ideas. Thanks again Wayne!