I have some experience with a large peoplesoft system where they claims
thousands of tables. Turns out almost everything I needed was in less than
10 tables.
Basically most of the tables were lookup tables used simply as key:value
store for the possible values of a field. For example table GENDER
{'M':'Male','F':'Female','O':'Other'}.
So 10 tables with 100 columns each and one lookup table for each column
gives you more than 10,000 tables. I suspects that is your case too.
All systems that claim an insane amount of tables belong to this category.
The way to handle it is to load all lookup tables in cache and use cache
instead of database access to convert key<->value. In fact the values for
lookup tables almost never change.
I suggest before you embark in this venture do the following exercise: make
a list of all table names. For each table make list of fields in the table
and count the number of records (more or less).
You will find many tables with less then 100 records and less then 10
columns. You will find a few tables with more than 10 columns and more then
100000 records. You need to find out how many tables belong to one category
and how many to the other.
If this is the case, as I suspect, than you can use web2py but you need to
setup some clever caching system to hable the lookup tables. It would be
the same with other frameworks since you don't want to join everything all
the time or your database will grind to a halt.
It is also possible I am completely wrong in my assumption.
In the case of the peoplesoft system I studied they also were storing all
past versions of each record in the same table as the current record.
Basically every record had had two dates (valid_from, valid_until). Current
records had valid_until set to 2999-12-31. records would never be modified.
The process for modifying a record consisted of creating a copy of the
current record, editing the copy, setting the valid_until=now for the
previous current record, updating all references pointing to the record. Af
course all tables used the same mechanism for versioning thus making the
update process very slow and cumbersome, and all tables un-necessary large.
Yet this simplifies auditing because you can go back to any moment in time
simply by filtering records in a query.
The reason I am explaining all of this is that probably you are going to
have to deal with something like this. The problem is not web2py vs other
framework. The problems will be that you need special logic to handle those
tables which is foreign to web2py and many modern frameworks which simply
assume more moder database design practices.
My suggestion is start small and see what happens. Find who are your
primary target users. Find which tables they need to access and create a
web interface for those tables. You will probably be able to factorize the
interaction with the database in many small apps.
Massimo
On Friday, 28 December 2012 09:38:25 UTC-6, Alex Glaros wrote:
>
> Can web2py be used for highly complex relational databases for large
> fiscal projects? Example: California's Fi$cal project -
> http://www.fiscal.ca.gov/<http://www.linkedin.com/redirect?url=http%3A%2F%2Fwww%2Efiscal%2Eca%2Egov%2F&urlhash=DBJm&_t=tracking_anet>
> - with roughly 10,000 tables and many complex joins.
>
> What components of web2py would start to get slow or not work well when
> having so many tables?
>
> If web2py would instead be better used to prototype the Fi$cal system,
> what would be good production-version candidates to migrate to? Pure Python
> using sqlAlchemy? Java? Anything that would make migration easier such as
> Python-based frameworks?
>
> Thanks,
>
> Alex Glaros
--
