Multiple tables versus mutiple databases
Posted by:
Date: December 16, 2008 11:44AM

In a setting of a web based application (typical LAMP installation) where an individual user logs into the app and accesses data for one of numerous subgroups, each of which requires about 2o tables to run the application, which design will perform better:
A) a single database with a set of identically structured tables for each subgroup differentiated by table name or

B) a database for each subgroup, each with the same table schema.

In either case the tables any individual web user will most commonly be accessing for their group have ten's of thousands of records, the remaining 18 or so required tables having group specific variables and data to control the application. In all cases the web application interface uses the same php code.

In case my question isn't clear scenario A would have a single database with a master table that controls the group numbers by and id, say 1 to 20 and then a set of tables for each like (users_1, data_1, ...), (users_2, data_2, ...),(users_20, data_20,...). In this case the single database would have in excess of 400 tables.

Scenario B would have a master database with a table to store individual group id's and each group id would have a database with a name based on their id like db_1, db_2,...db_20. Within any db_* there would be identically structured tables user, data, ... . In this case there would be 21 databases each with only 20 tables.

Thanks

Pat

Options: ReplyQuote


Subject
Written By
Posted
Multiple tables versus mutiple databases
December 16, 2008 11:44AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.