SQLite Forum

Placeholder for table name when inserting data?
I'm by no means a database expert, but having a hundred tables is _very often_ not the best way of doing things. If your 100 tables are essentially storing the same (or very similar) types of data, it is _often_ better to put all records in the same table, with an additional field to indicate which "table" it came from.

For instance, in a manual system you might keep a list of books on Sci-Fi, a list of books on history and another list of books on cooking. In a database, it is _usually_ better to have one "Books" list, with a field indicating which type of book it is (often as a "type code": 1=SciFi, 2=History etc.).

Similarly, if the original tables are split by time (e.g. a different table for each month), then it is usually better to use one database table with a date or month-number field.

With a (semi) manual system, it's can be easier to have separate lists, so you're not hunting for the entry you want among lots of irrelevant entries. With a database, it's (usually) just a matter of adding `WHERE BOOKTYPE=2` to the query.

Example benefits of reduced numbers of tables (apart from the "don't repeat yourself" practice you mention) include: it is easier to make cross-subject queries (how many books _in total_ were published before 2000); it is easier to change how the data is stored (say you suddenly decide you want to record the number of pages in each book). With 100 tables, you'd need to make 100 queries and combine the results, or make alterations to 100 table structures. With one table, things are much easier.