Need project design advice for parent-child relationship

[David]

We are still pretty new to JIRA, we know the features but don't know best practices yet, and are designing a new database to track data that has a parent-child relationship.  

To simplify the explanation imagine we are a manufacture of a variety of items and we are tracking customer orders.  Customer6 places an order on a given date and its composed of 5 nuts and 20 bolts, etc.  The challenge is we have to track various details about every single item (e.g. 10 of the bolts have to be marked as 'back-ordered).  So we definitely need an Issue record for all 25 items in this order.  And we don't want to repeat the Customer name and mailing address and other information in every single Item record.  So like with a relational database we are trying a Customer Issue Type as the parent, and an "ItemOrdered" Issue Type as a Sub-Task.  

This all seems pretty good on the surface but we need to do business intelligence analysis on various aspects of what's happening.  It's not clear to us how to export data like this, where we can include fields from the parent issue and the sub-task/child issue in the same line, which is what BI OLAP requires:

Order #6, Customer1, Nut Model X, 2/3/2018

Order #6, Customer1, Nut Model X, 2/3/2018

Order #6, Customer1, Bold Model Y, 2/3/2018

Order #6, Customer1, Bolt Model Y, 2/3/2018

Order #10, Customer2, Widget Model Z, 3/1/2018

etc...

Can anyone advise?

Thanks!

[Derek Fields _RightStar_ · Accepted solution]

There may be a plugin that can help you with this, but I haven't seen it. You can't think of Jira like a relational database, even though it uses one for storage. JQL only returns one type of object - an Issue. You can't "join" across issue types to enrich one issue with data from another without actually replicating the data, as you indicated in your question.

I can think of three ways to address your need:

1) You can use a BI tool like Arsenale Dataplane or EazyBI that attaches directly to the database and can access the data in a more relational way

2) You can export the data into a spreadsheet and use LOOKUP formulas to enrich child tasks with information from parent tasks

3) You can use Insight from Riada to create the non-issue Objects (Customers, Parts) and reference them from Orders. 

[David]

Thank you!  I have installed EazyBI and done some quick experiments.  So you believe that it will allow me to "join" subtasks with tasks, thereby including some task fields in with subtask records?  I've not yet seen how to do that.  I'm guessing it's maybe going to require some custom SQL.  That's fine with us if it solves this problem.