App Planning Assistance Request

[Berkey]

I am designing my first real Fabrik based app. My previous experience was a very simple single form based records system, so I could really use some feedback and guidance when it comes to very basic planning. The picture below illustrates the tables that I think will be necessary. The ones shown in red have one or more fields coming from another database through the use of a database sync tool that can put the data into a table on the Joomla database server. I created the diagram in Visio and don't know how crow notation works so the connecting lines are only to show basic interconnections and not the proper one to many types of notation. If someone lets me now the proper use of the feet and cross lines I can make changes to better illustrate relationships.

The purpose of this app is to track inventory as it moves through a testing process with a tie in to rs tickets in order to assign test jobs to technicians. It will also need to track groups of devices listed in a receiving document table in order to send out an email with a link to the test results once all devices listed on a receiving document have been tested. I would also like to "auto generate" tickets in rstickets either by sending an email or directly manipulating tables for rs tickets for each device once their location has changed to "In Lab" and they have the untested attribute set to true. I would also like to control the destination of files saved that are associated with either a test or at the device level, I am not sure which association makes more sense, but either way I would like the files to be saved in "\Manufacture\Model\"modelnumber-serialnumber-dateofcreation-incrementingfilenameversionnumber.fileextension".

An area that I am unsure of how to accomplish gracefully has to do with device attributes. I was thinking of having a list of all possible fields that could be used store results after a test. Not all devices are equal, so one device model might have a license feature or a memory size field that would be relevant, but another device model may not have them. So I was thinking that I could have a table with all the different possible fields listed in them and then create model specific tables that would link to it and store the results there. My vocabulary may be coming up short as I write this description.

I know this is a mega post, but I am biting of quite a bit and would welcome any insight or guidance at this stage that I can get so as to maximize the quality of the app. I would be more than open to a Skype session or another direct communication means in order to have a more dynamic discussion of this project. I welcome all questions and feedback.

Viso available here:
http://wikisend.com/download/735928/Database Relationships.vsdx

Skype:
Mr.Berkey

 

[robhparker]

As far as the relationships notation I used a tool called gliffy it is a google app. It explains each one in the tooltip. I would have given visio a look if I didnt hate Microsoft so damn much!

Have you worked with cascading dropdown element to see if it will accomplish what you need for the device attributes? It would be no doubt pretty elaborate but it may do the trick. I will need to figure out something similar for my next fabrik adventure if I can ever get my current one put to bed. I hit you up on skype. Please share what you find out or if you get stuck, it seems you and I are in very similar positions.

Question:
What database sync tool that can put the data into a table on the Joomla database server are you using?

 

[Berkey]

I looked into the cascading dropdowns after you mentioned it. I think it won't fit my needs here, but will likely come in handy for another task. I think my idea of having a table listing possible attributes and having them used with the model table in order to define input types and such would be kinda over the top. I will need to rethink my approach.

The database sync tool is called DBSync for MS SQL & MySQL found here: http://convertdb.com/mssql/mysql I needed a solution that would let me read data from our inventory management system's MS SQL database. I didn't know any other way to have Fabrik use the data then to replicate it into tables on our Joomla MySQL sever. At this point I have the program pulling data from the MS SQL server, but not pushing any back. To be honest making changes to data stored in the MS SQL database from Fabrik is a goal but only on a very limited basis and very carefully.