Can somebody educate me in this subject ?
How much better is terms of efficiency, security and convenience to have a single sqlite file vs multiple sqlite files ? (considering it’s going to be one app, managing the data) . This is a medium sized db I am talking about. Not millions, but probably hundreds of thousands of records…
Forgot to say… In the case there is more than one user connected it will use CubeSql and it will never have more than 5 users at the same time… max.
gracias
R
If you are talking about a “flat database” vs a “relational” database, a relation one is usually a better choice (unless its REALLY small)
The disadvantage to a “flat” structure is you must REPEAT all common data values…
Lets say you have a “full_name” field… and a person could have 1000 attributes… the flat version would have “Dave S” in there 1000 times, where a relational type would have a “key” value in one table, and that same key and the name in the second table
Advantage is database foot print… the “keys” are much smaller than the data element it refers to, and if I wanted to change “Dave” to “David”… I change ONE record in ONE table, not 1000 records . As well as infinite scalability
Basically look at your data requirements… if everything fits a ONE TO ONE model, and you know it always will fit that model, than a flat database structure would be fine. but if you have a ONE TO MANY or MANY TO ONE, then design it as relational from the beginning and save yourself the headaches of trying to re-engineer it sometime in the future.
There is a speed penalty, but it is so small you probably won’t even notice… The other “con” to relational, is the SQL is a bit more complex… but that can also be a “PRO” as you have tons more flexibilty in what you can do with the data.
I have designed relational systems that contained 100’s of tables, most of which had less than 20 columns, and various queries simply used only those tables that were relavant to the specific process…
Names were in one,
Addresses in another
Eduacational Degree in another (not everyone had one)
Medical Specialties (could be one, could be hundreds per person)
to do this as a flat database would have been thousands of columns, of which 80% would proably have been blank…
to add an address, I just added a record to the Address table… didn’t have to add a new column to a flat table
Dave, thanks a lot for the detailed answer… The DB model I am using is a relational DB…
My question was geared towards the distribution of tables over different files, to avoid the “100´s” of tables, (and 20’s or more columns) because in my ignorance I have the fear that that could be too much. So maybe, I thought, one file for business data, other for customers related files, and another one for general reports… (that was just an example)…
note: by file a mean a new DB connection, as well
Thanks a lot
R
Ah… ONE Database, multiple tables … YES
Multiple Databases (only if you cannot avoid it)