How to display ( and work with ) related data without using a table component?

Question

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

• 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

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?

Answer 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.