Best Practice to Use Views or Tables with SQL Databases and BOs


Author
Message
Ivan George Borges
Ivan George Borges
Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
Hola Juan.

Si, prepararé un ejemplo para usted y pondré aquí.

Juan Carlos Pazos
Juan Carlos Pazos
StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)StrataFrame User (310 reputation)
Group: Forum Members
Posts: 144, Visits: 227
Que tal,

Sería factible que pusieras un ejemplo de tu proceso, la parte del View la puedo manejar pero el proceso de filtrar duplicados y demás no se me ocurre como hacerlo.

Gracias,

Smile Everything is possible, just keep trying...

Ivan George Borges
Ivan George Borges
Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)Strategic Support Team Member (4.9K reputation)
Group: StrataFrame MVPs
Posts: 1.9K, Visits: 21K
Hey Russel.

I think that a good time to use a view is when you need to use the BrowseDialog to search on different tables at the same time. So, if you have a form with a Parent BO and 1 or more Children BOs related, you could create a view to join all these tables and format the BrowseDialog to search on that view, so the user would get all the columns needed from the different tables. Once the BrowseDialog is Closed, you can deal with the view copying the data from the populated BO to another without duplicates on the Parent PK, get an array of the PKs populated and Fill your form Parent BO with the array of PKs you've just created.

I have found this a great way to extend the use of the BrowseDialogs.

Aaron Young
Aaron Young
Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)Advanced StrataFrame User (569 reputation)
Group: StrataFrame Users
Posts: 277, Visits: 1.1K
Trent is right.

I came from the same VFP/VPME background and while it may work on the principle that virtually every database access is through a view, the same (thankfully) is not the same with SF and .NET.

When I initially looked at SF I was nervous of losing the xCase2VPM view generator but I have never looked back since. I now have a large VPME application converted to SF and there are only 6 views now when there were more then 700 in VPME. Data access is so much easier, more flexible and faster than with VPME. I really can't say how much better SF's data access is over VPME/xCase2VPM.

The best advice I can give you is to forget about VFP/VPME when working with SF. Views are for special occasions - eventually you will know if and when you need one.

If you want to talk more about this feel free to contact me.

Trent Taylor
Trent Taylor
StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)StrataFrame Developer (14K reputation)
Group: StrataFrame Developers
Posts: 6.6K, Visits: 7K
We have a long session in our training class that covers a lot of this.  But is short, you do not want your entire application to be view driven.  Views are slower and will have a drastic negative effect on your application if you were to have nothing but views.  Does this mean that you should never create a view? No.  Not at all.  There are times for views, for example reporting, or when you have the need to a more complex situation.  But you can never make views as fast as optimized SPROCS.  So the short answer here is, especially since you are just getting started here, to stay away from views unless an absolute specific need arises. 

Let me put it like this, in our medical application we have 250+ tables in one database, very large databases (millions of records in single tables) extremely complex real-time calculations, and through all of this, we never use a view.  We do have one or two views for RBS security, but past that, it is all tables, sprocs, and UDFs.

Russell Scott Brown
Russell Scott Brown
StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)StrataFrame User (424 reputation)
Group: Forum Members
Posts: 124, Visits: 597
Coming from a VFP and VPME background, I am wondering what is the best practice for applications that use SQL Databases.  In VFP applications I normally use Views for Forms and Reports and separate them into two or more databases.  Also, I would try to use Views instead of Filters most of the time.  Is the way I would do it in VFP still the best way for SQL database applications?

- Is it best to separate tables and views into separate databases?  I noticed the Strataflix and Strataframe samples both show views and tables together in the same DB but this may be just for simplification

- Should Maintenance Forms be based on views or tables?

- Should Reports use views or tables?

- When is it best to use a View as opposed to a table or a table with a filter?

- Also, I plan to develop applications using SF that I want to be able to sell just parts of as well as the entire application as a whole.  For example, I'm working on a Safety Review application that could be sold as a complete package or someone might just want to purchase the OSHA package, ADA package, Driver/Employee File package, Vehicle Inspections package, etc.  I might also have a Lite/Express version too for example.

- I'd be interested how others are doing it and what make the most sense in terms of flexibility, especially since I have the opportunity to start from "scratch" so to speak.

Thanks!

Russ Brown (Using C#2010/SQL Server 2008)

GO

Merge Selected

Merge into selected topic...



Merge into merge target...



Merge into a specific topic ID...




Similar Topics

Reading This Topic

Login

Explore
Messages
Mentions
Search