How best to handle multi-step insertions on multiple 1 to Many Tables

[padlocked17]

Good Afternoon All,

Working on an app that has 6 major data types. Plans, Categories, Requirements, Solutions, Programs, and Vehicles. I have a plethora of 1 to Many tables that capture the relationships between these major types of data. What I'm trying to do next is build a multi-step form (that will utilize a tab control) to walk through building the 1 to many tables to capture a relationship between the data types. The data is fairly hierarchical (ie. a Vehicle must be assigned to a Program, a Program must be assigned to a Solution, a Solution must be assigned to a Requirement, a Requirement may be assigned to a Category, A Category to a Plan, etc.

Looking for ideas on how to implement a way to conduct a multi-step approach to building out the relationships to existing data and also allow for the creation of records at each step along the way.

Ideally I'd like to capture the user inputs and make all of the changes at the end, so that if they cancel the wizard, no changes would be made.

Any thoughts at all on how to best approach this? I was contemplating temp tables to capture all relationships built in 1-many tables and also temp tables for any newly created records, then running a mass insert at the end of the process. Wasn't 100% sure about the best way to handle the complexity in the multi-step process. I'm assuming just a VBA routing to display or not display tabs on a Form Control?

 

[Uncle Gizmo]

Any thoughts at all on how to best approach this?

It just sounds all wrong....

Like your initial design was flawed and you have kept going with it and ended up in a corner.

I have no idea about your database, it might well be perfect, but from what you describe, it just sounds like a big problem....

 

[June7]

You show 6 data entities and you say they are hierarchical. Forms can be nested to about 7 levels.

Otherwise, an unbound form with VBA to save data is an option - however, using autonumber keys to relate data complicates this approach. I have never used unbound form for data input.

 

[jdraw]

I think the best place to start would be to describe the business (processes) in terms of the subjects you have identified.
The business processes/rules identify the relationship(s) between the entities/subjects/tables. You need to structure the tables and relationships to support the business as a data model. You can test the model with sample data and sample scenarios. This will give you a chance to vet and adjust the model which will become a blueprint for your database.

 

[padlocked17]

It just sounds all wrong....

Like your initial design was flawed and you have kept going with it and ended up in a corner.

I have no idea about your database, it might well be perfect, but from what you describe, it just sounds like a big problem....

I know it sounds wrong, but I'm pretty confident in the normalization of the design. The issue is that the data for the project is fairly complicated with a lot of messy relationships that can exist. Lots of many-to-many relationships that I need to capture, but the business rules of the organization drives some defined relationships amongst these 6 major data types.

I could incrementally only allow forms to be built to create the connections that are allowed, but I'd like to have a "Wizard" like interface that takes me from start to finish.

 

[padlocked17]

I think the best place to start would be to describe the business (processes) in terms of the subjects you have identified.
The business processes/rules identify the relationship(s) between the entities/subjects/tables. You need to structure the tables and relationships to support the business as a data model. You can test the model with sample data and sample scenarios. This will give you a chance to vet and adjust the model which will become a blueprint for your database.

Absolutely. Did this and the model support the business rules ... now it's just trying to streamline the interface to make it as easy and user friendly as possible.

 

[padlocked17]

This will be a nightmare to implement if there is any chance at all that multiple people can be performing the process at the same time because of the autonumbers. And in a multi-user environment, you ALWAYS have this problem unless you use a queueing table where you can lock out everyone else from a specific process until person1 is done.

Are you trying to build an interface to create data that doesn't yet exist or are you trying to move existing data around which is a whole different problem.

That is an extremely valid point and something I will have to consider.

The interface is intended to be used with existing records, and just to create an entry in a join table to create the relationship. The idea then was while we were going through a multi-step wizard to build the relationships, if the records didn't exist, I was wanting to create the record and insert into the join table at that point the correct references.

 

[padlocked17]

You show 6 data entities and you say they are hierarchical. Forms can be nested to about 7 levels.

Otherwise, an unbound form with VBA to save data is an option - however, using autonumber keys to relate data complicates this approach. I have never used unbound form for data input.

I don't think I'm looking at a nested form approach. Anytime the 6 entities can be related, they are always in a many to many relationships so I have join tables for every instance where they can be related. I'm just trying to think through a neat and nice way of allowing the user to start at the bottom of the "logical" relationships and work their way up. I could easily require the user to go into each entity type and create a record from each form to the respective join table. I was just hoping to do it in a multi-step wizard approach (ie. you review and create a record for the first many-to-many). Then after that you move to the next level up and create the next many-to-many via a join table and so on.

 

[jdraw]

Absolutely. Did this and the model support the business rules ... now it's just trying to streamline the interface to make it as easy and user friendly as possible.

Perhaps you could post the model and comment on your proposed activities in some sort of priority.

 

[June7]

Yes, you can build your 'wizard', most anything can be done with enough code. Again, using autonumber key complicates.