March 21, 2002 at 10:07 am
What are the pros and cons of using SQL Server for storing "session data" for an ASP application that collects data from several forms, combines the data and then ships the information off to a mainframe legacy application for processing?
In particular, should the underlying SQL tables be normalized or is it OK to have the equivalent of a "flat file" to hold the session data.
Should constraints and relationships be defined in the database or should the application be allowed to implement these?
Are there any other options besides using SQL Server to hold the data?
Any feedback on these issues would be greatly appreciated!
Paul
March 21, 2002 at 10:36 am
Well with storing the data in SQL you potentially cause a lot of overhead in various areas and could lock periodically causing user frustration. I would look into a xml method to write data to a flat local file if you are just trying to collect it quickly and do not need to do anything special for the customers view, then pull into SQL periodically to move to mainframe. Can you be more specific though so the advice you get fits your needs, things like connection data, store what was viewed, must be real time on mainframe. Just tell us more so we can help you best.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 21, 2002 at 11:05 am
The application in question is an application form that collects information about a company, the company owners, the location, number of employees, tax info, etc. It requires several ASP pages to collect all of the info. This info is then sent to the mainframe thru COMTI (Host Integration Server) to a CICS transaction. CICS processes the data and returns an accepted or rejected status to the ASP application. There is also a "Print Preview" feature that assembles all of the information into a "printed form" format for review by the user prior to submittal. Once submitted, the "session (form) data" is no longer needed and is deleted.
The developers refer to this data as being "transitional" and should not be required to adhere to database standards that other OLTP databases must follow.
The kicker is that the submitted data is maintained in a table and is retained for up to 30 days so that the they can see exactly what data was submitted. To me this changes the definition of the database from being "transitional" to being a real "data store".
Hope this helps clarify the functionality.
Thanks,
Paul
March 21, 2002 at 11:28 am
I don't see anything wrong with putting in the db. What structure you use will depend on your needs. If a flat table will do it, why not? If you can see that putting it into separate tables to make it easier either to work with in your app or when loading to its final destination, thats good too! Anything that is going to be persisted beyond the session makes sense to put in a table. You might consider using XML to hold the data during the session, then persist it to a table (either as XML directly..not my favorite, or rip out into rows using OpenXML).
Andy
March 21, 2002 at 11:47 am
Hi Andy,
Should the "persisted" table be required to adhere to the database standards (ie be normalized to 3NF (normally), have constraints defined, follow our published data standards)?
We see this as being an ongoing problem with developers taking the "it's transitional" side of the fence with the DBA's trying to enforce the standards. Without a proper definition of what is "transitional" versus what is a normal data store, chaos shall prevail 😉
Thanks,
Paul
March 21, 2002 at 1:32 pm
At TechEd 2000, I heard a presentation about Microsoft.com. They talked about designing a stateless system because the use of "session" variables inhinbits your scalability. Each user is then "bound" to a server.
Instead, what they did was store an XML blob for short term information, like a user's first visit or when the user was not indentified. Once the user is identified, they used normalized tables.
I thought this was an interesting technique for building a site.
Steve Jones
March 22, 2002 at 5:42 am
I think there was an article in MSDN Mag last year about it - Johnny Papa wrote it I think.
Whether or not you apply true db standards I guess depends on who is going to do the work! For truly temporary data I don't think it matters at all, use what works, including XML in a blob. If you're really using the browser as a data entry tool for data that will get loaded into your mainframe then I think you need at least some constraints if not full 3NF. Ideally your server storage format would match the mainframe so that you'd just be doing simple appends/updates. In practice I imagine it's not that easy. It's a lot of work to normalize a flat table for loading compared to having it done as part of the development process. In the end it's probably the same amount of work, but who does it?
Andy
March 22, 2002 at 5:43 am
March 22, 2002 at 6:08 am
Hi Andy and Steve,
Thanks for the great feedback. I have proposed to the DBA group and the devlopers that they look into the XML blob technique. That model really fits both of their needs.
The browser really is just a data entry tool. But... the developer said that the "flat file" database design that he has created is in 5NF... made possible by his application code! ROTFLMAO (well I wanted to be!) when he said this. Anyways, they do not want any constraints or relationships defined in the database because "all of the data validation checks are done on the mainframe and we do not want to recreate that application".
The XML blob works for me... we'll see if it works for them.
Thanks again!
Paul
BTW - great site that you have here!
March 22, 2002 at 11:27 am
Glad we could help. Visit often!
Andy
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply