I have a moderately complex mfg line I am trying to create a data model for, at least using Tulip as the front end for. One of the requirements of this data model is that it be able to support ‘recursive’, or ‘self-referential’ tables. Something along the lines of the ID of one table row may be a subcomponent in another table row, ad, infinitum (really only to a depth of a maximum of around 10 or so).
I have two questions in regards to this:
Can I do this with Tulip’s native Tables???
Can I use an existing SQL Server backend, and using the TULIP SQL Server connector use it to store most, if not all, of my data in?
I am fairly new to Tulip but impressed by its agility, and the speed and ease with which powerful functionalities can be created. I am considering building a SQL Server backend with Tulip handling all the front end presentation, logic, and conditions. Is this possible with Tulip??
I saw your post and recognized that I am dealing with similar design aspects in tracking data in Tulip Tables and have needed to reference a SQL engine backend.
Regarding Tulip’s ability to reference tables, there’s been a lot of recent work in the development of Linked Tables. So far I haven’t gone too deep but this function allows for Tulip Tables to have one-to-one, one-to-many, and many-to-one relationships including self referenceing the Tulip Table itself.
Regarding the SQL ability, I have been adding a column in a Tulip Table that contains the ID of a SQL table that I use to reference when I use a SQL connector for our backend.
From my experience using a direct SQL connector is good for testing raw SQL (i.e. INSERT INTO ___) but feel having your backend built up with stored procedures will be easier to validate and error check when in production.
Right now I’m preferring using HTTP connectors to an endpoint that will do the SQL heavy lifting as this seems to add more scalability rather than just calling the stored procedures or raw SQL, but that might be different in your use case.
Thanks Risom! I agree that Stored Procedures and/or Web Services are the way to go. You also answered my questioned, via the illustration you provided, of if Tulip native tables can be ‘self-refererential’. You also showed me that I can link to a SQL table to a Tulip table. Just to clarify, that link is a link to an specific row ID in the Linked SQL Table, right???
I guess I am still wondering, though, if I have to use Tulip tables for at least some of my backend work, or if I can go directly to a SQL or HTTP connector?? I would imagine that Native Tulip tables are more responsive.
Thanks again Risom! You have been helpful and if you get a chance to address my additional questions/confusion I’d appreciate that too.
I assume then, that we can call stored procedure in SQL Connectors, passing in Tulip variables. Can we also pass in values from Tulip Tables? Also, can we then look at the result set(s) from a stored procedure call?? Is there any Tulip University course on any of this?
Correct, the SQL ID column in my example picture is the ID of a specific row in my backend SQL database however it is not directly connected to the SQL DB and is dependent on connectors.
Regarding using Tulip to carry some of the backend work, I believe you can use it to track information not relevant to your SQL DB but information you feel is relevant to the frontline ops. Another possibility I’ve been exploring but I didn’t show was using the SQL connector to instantiate the ID column of the Tulip table rows and mirroring the backend SQL IDs to eliminate the possibility of redundant records.
Adding columns to SQL DB Tables isn’t trivial but I feel Tulip offers a more agile way to track records and doesn’t require the overhead of DBA to create tables and columns.
Regarding passing Tulip Table Records, when you’re building an App you can reference table records, variables, or even static values and pass it’s information along to a SQL connector.
Here I am passing variable, but could do pass any of the listed options. I then save my returned values as a tulip object variable (basically JSON object). I could also save it as an array or really long string.
Thanks Richard. Yes, that answers more of my questions and also suggests other possiblities (database mirroring or partial mirroring), that I had briefly wondered about.
I’m sure I’ll have more questions in the future.
One issue, partially related to this discussion, is one we are having internally about the issues vs. value of having a cloud-based solution, such as Tulip. It seems to me that we could, if we so decided have a hybrid environment, possibly with SQL on site. I also ran across another Tulip Knowledge Base article about having onsite OPC UA connectors to SCADA and PLC devices as I copied in the link and diagram below.
