2

I'm working with an external system where users can create entities and link them bidirectionally. One entity is the source / the parent and the other entity is the target / the child. So one link will be Parent -> is parent of -> Child and the other link direction is Child -> is child of -> Parent.

Most users using such systems are working with tools like Excel and setup their workflows using tables because auditors must review all the data later on.

Inside a sheet you begin with a "leading element", other columns are related to this "leading column" based on their relations. Given the following example:

  • Leading type: Manufacturer

  • Following type ( parent ): Store ( selling products from different manufacturers )

  • Following type ( child ): Product ( from manufacturer )

  • Following type ( child of store ) : Employee

with the data

  • Manufacturer 1 is selling products in Store 1, 2 ( with Employees 1, 2 ) and owns products 1, 2, 3, 4, 5

  • Manufacturer 2 is selling products in Store 2 ( with Employees 7, 8 ) and owns products 1, 4

Based on the requirements users create their Excel sheets to fit their needs. I created a table component that would generate a table similiar to this one

enter image description here

  • The column order may vary

  • I'm working with rowspans to span "parent" elements across their children

  • It is not necessary that the app runs on mobile devices ( no responsive design / desktop browser app only )

  • There is quite more things to do. Entities have fields, some are displayed in the cell.

Now the tricky part: I don't like that table approach, it's hard to calculate and quite messy. Imagine that each manufacturer is selling n products in m stores with k employees ( and there might be 30 more columns ) that table would be gigantic. And no one on earth would read 30.000 lines of data.

I know, pagination, sorts and filters help but I'm thinking about a different visualization. The tool Gource inspired me

enter image description here

but the problem is that so many relationships would produce "too many" lines on screen. And you wouldn't be able to display the entity fields ( maybe on hover, but you can't display entities with fields in a good way ).

And most users are working column wise, they write down each manufacturer first, then each store, ...

I thought about using something like stepper components ( https://vuetifyjs.com/en/components/steppers/#usage ) but they are not able to deal with multiple outgoing branches ( relationships ) and users are loosing the big picture ( the rest of the data flying around )

Do you have any ideas how to visualize such relationships for users focusing on tables and tools like Excel?

5
  • Who are the users? And how do they interact with the information? Jan 25, 2023 at 22:34
  • "Manufacturer 1...owns products 1...4... Manufacturer 2...owns products 1, 4" That seems to contradict. Can you clarify what "owns" and "products" mean? And what is a product the child of? Store? Or Manufacturer? Jan 25, 2023 at 22:37
  • Are you looking for a Tree Grid, maybe?
    – Andy
    Jan 26, 2023 at 8:40
  • @bloodyKnuckles If a product isn't a single article but a type (e.g rubber boots, wherever they come from) it can be owned by/come from different manufacturers. Feb 28, 2023 at 2:02
  • @Andy The Tree Grid looks great. I didn't know that, too. Thx for the hint. However, apparently it's just for hierarchically nested 1:n relationships: "A treegrid widget presents a hierarchical data grid [...]. Any row in the hierarchy may have child rows, [...]". The relationships the OP describes are much more complex. Feb 28, 2023 at 2:09

1 Answer 1

1

According to the infos in the question and if there aren't any missing there exist the following relationships:

  • Store – m:n – Manufacturer, derived from:

    • "Store ( selling products from different manufacturers )" → 1:n

    • "Manufacturer [...] is selling products in Store[s] 1, 2" → 1:n

      two 1:n relationships in the opposite direction → m:n.

  • Manufacturer – m:n – Product, derived from:

    • "Manufacturer [1, 2] is selling products"
  • Store – m:n or 1:n – Employee

    This is not completely clear because under "with the data" there's:

    • in Store 1, 2 ( with Employees 1, 2 ) → m:n

    while in the table:

    • rows 3 and 4: Store 1 has assigned Emp 1 and 2 → 1:n
    • rows 5, 6, 8, 9: Store 2 has assigned Emp 7 and 8 → 1:n

Basically a single table (like in XL) isn't the proper tool to hold and show such information clearly and precise because according to the snippet in the question:

  • In Store 2:
    • rows 5 and 8: Emp 7 sells products 1 and 3 only and
    • rows 6 and 9: Emp 8 sells product 4 only

I don't think that reflects real life AND there's also a contradiction for the second point:

  • According to cell A6 Emp 8 sells in Store 2.
  • According to cell A9 Emp 8 does not sell in Store 2, since the cell is not merged with A8. (But that could be "just" a formatting mistake.)

I agree with "that table approach, it's hard to calculate and quite messy" if I'm allowed to replace "quite" with totally.

So, first things first:

  • Design an Entity–relationship model on paper (or in an according app).

  • Select a tool that's made to handle entities and their relationships by design: A relational database.

  • Represent your entities (Store, Manufacturer, Product, Employee) with tables.

  • Establish proper relationships between the tables.

    Generally: In case of m:n represent their relationships with intersect tables.

    In your special case:
    Since there's just one attribute for each entity: a number, you'd just need (depending on "Store – m:n or 1:n – Employee" above):
    - three intersect tables (StoreManufacturer, StoreEmployee, ManufacturerProduct) or
    - two tables (Store, Employee) and two intersect tables (StoreManufacturer, ManufacturerProduct).
  • See how you can get the info your users need out of the tables (with their relationships) with an appropriate query.

  • Then (and only then) think of how to display this info to your users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.