Re^2: DBI Update dynamically

Hello again Anonymous Monk,

Since there is no limit on how many tables can SQLite see here Limits In SQLite, why not create different table for each user and then use the REPLACE that fellow Monk poj proposed. By doing so you can have different number of columns for each user based on their requirements.

Hope this helps. BR / Thanos

Seeking for Perl wisdom...on the process of learning...not there...yet!

Comment on Re^2: DBI Update dynamically Select or Download Code

Replies are listed 'Best First'.
Re^3: DBI Update dynamically by stevieb (Canon) on Feb 24, 2018 at 01:02 UTC
I feel that having a different table for each user completely removes the entire purpose of a single source of information. Now, it feels like I'm missing something here, but to maintain such a structure (especially to simply fetch information across all tables) would be a nightmare. I'm not saying its right or wrong, I'm saying it feels like a kludge that actually goes against any efficiencies and scalability. The real solution I think is to add a bit more code and do the UPDATE accordingly. That'll keep the DB consistent, and allow OP to still dyanmically do things in a way that would be scalable going forward. I just got to a hotel after a three hour drive so I need more time to digest this (perhaps OP can share more insight into the reason for needing this extreme conciseness) before I have any formal solution. Could be trivially easy to add another column to the table, perhaps even comma separated that explains which columns each user can populate by name, then grab that info based on the userid, dump it into a hash, and then you know which columns user X has, and just dump `undef`, NULL or whatever to the others (again, using UPDATE).	[reply] [d/l]

Replies are listed 'Best First'.

Re^3: DBI Update dynamically
by stevieb (Canon) on Feb 24, 2018 at 01:02 UTC

I feel that having a different table for each user completely removes the entire purpose of a single source of information.

Now, it feels like I'm missing something here, but to maintain such a structure (especially to simply fetch information across all tables) would be a nightmare.

I'm not saying its right or wrong, I'm saying it feels like a kludge that actually goes against any efficiencies and scalability.

The real solution I think is to add a bit more code and do the UPDATE accordingly. That'll keep the DB consistent, and allow OP to still dyanmically do things in a way that would be scalable going forward. I just got to a hotel after a three hour drive so I need more time to digest this (perhaps OP can share more insight into the reason for needing this extreme conciseness) before I have any formal solution.

Could be trivially easy to add another column to the table, perhaps even comma separated that explains which columns each user can populate by name, then grab that info based on the userid, dump it into a hash, and then you know which columns user X has, and just dump undef, NULL or whatever to the others (again, using UPDATE).

[reply]
[d/l]