I should add that in the system I have looked almost all tables where
de-normalized. All tabled storing a key would also stored the value
corresponding to the code. This is for two reasons: 1) if you have
thousands of tables you cannot join everything all the time. 2) for
auditing purposes it should be possible to change a value in a lookup table
without changing the corresponding previous values of records created
before.
This means that while I needed the lookup tables for IS_IN_SET() IS_IN_DB()
validators, I never needed more than two joins.
Again, the all design seems crazy to many of use but it has its plus sides
from an auditing point of view.
Massimo
On Friday, 28 December 2012 22:39:51 UTC-6, Massimo Di Pierro wrote:
>
> 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
>
>
--
