I've got a database which logs modification records right into a table. This modification table consists of foreign secrets with other tables (the alteration table only consists of references to things modified). Objects within this modification table could be arranged into different populations. Whenever a user access the service he only demands the database for object on his population. I'll have a couple of to 10 new populations every week.
- This table is asked for by mobile phones super frequently and can consists of about 500 000 / 1 000 000 records.
- Basically split the alteration table into many tables there's no table-join to complete to reply to user demands
Basically change this single table into many tables, I suppose it'll speed the response time.
But however, each "place" within the modification table should have first the title from the target table (it suggests another request). To do this, I plan to possess a column within the "population" table having a varchar representing the prospective table for modification.
My real question is a design-pattern / architecture one --> Must I get a single very huge table with 3 "where" for every request ? or must i provide a attempt to many light table without any "where" to experience ?
Interesting helpfull help on this type of tricky choice
The cleanest factor is always to play one table and partition it around the populations. Partitions are created with this.
500K - 1M records is not trivial - however it certainly is not huge either. What's your database platform? Most mainstream professional platforms (SQL, Oracle, MySQL, etc) tend to be more than able to handle this.
When the table under consideration is narrow (has couple of posts) then it's less apt to be an problem than "wide tables" with a lot of posts.
Getting plenty of joins happens to be an problem (i simply can't speak from experience). For the way you manage things (and just how good the application code is) do you want the foreign-key constraints?