Could it be cost effective for a credit card applicatoin to produce database tables dynamically as a way of partitioning?

For instance, say I've got a large table "icons" having a "userID" column determining who owns each row. If the table tended to develop very large, wouldn't it seem sensible to rather possess the application produce a new table known as "icons_" for every new user? Think that the applying is only going to have to question for icons owned by just one user at any given time (i.e. you don't need to try to join these user widget tables together).

Carrying this out would split up the main one large table into easier-handled portions, but this does not appear as an elegant solution. I believe, the database schema ought to be defined once the application is written, and then any runtime information is saved as rows, less additional tables.

Like a more general question, is modifying the database schema at runtime ever ok?

Edit: This is mainly hypothetical I'd an excellent feeling that producing tables at runtime did not seem sensible. That being stated, we all do possess a table with countless rows within our application. Chooses perform fine, but such things as removing all rows possessed by a specific user can require sometime. Essentially I am searching for some solid reasoning why just dynamically developing a table for every user does not seem sensible when ever I am requested.

No, that might be an awful idea. Although some people might DBMSs (e.g. Oracle) allow just one table to become partitioned on values of the column, which may attain the objective without creating new tables at run time. With that said, it's not "standardInch to partition tables such as this: it is simply usually completed in large databases.

NO, NO, NO!! Now repeat after me, I will not do this because it will create many headaches and problems in the future! Databases are created to handle considerable amounts of knowledge. they will use indexes to rapidly find what you're after. think phone book how effective may be the index? will it be better to possess a different book for every surname?

This can not provide you with anything performance smart. Have a single table, but make sure to index on UserID and you will have the ability to obtain the data fast. if however you split the table up, it might be impossible/really really challenging any info that spans multiple customers, like search all customers for any certain widget, count of icons of the certain type, etc. you must have every query be built dynamically.

If removing rows is slow, consider that. The number of rows previously shall we be speaking about 10, 1000, 100000? What's your clustered index about this table? Would you make use of a "soft remove", where you've got a status column that you simply UPDATE to "D" to mark the row as erased. Are you able to remove the rows at another time, with less database activity. may be the remove slow since it is being blocked by other activity. consider individuals before you decide to split up the table.

Utilizing an index on userID should result nearly within the same performance.

For me, altering the database schema at runtime isn't good practice. Consider, for instance, security issues...

Could it be cost effective for a credit card applicatoin to produce database tables dynamically as a way of partitioning?

No. (smile)