0

I'm building an application where the user may create any number of web forms. The forms will subject a wide range of topics and may contain all kinds of fields, devided into any number of sub forms.

In order for this to not become a normalization issue, my application generates tables for the sub forms dynamically. The end architecture may look something like this (simplified):

/* Static table - hierarchical level 1 */
[ProjectTable]
project_id (KEY, AUTO INC), , project_name

/* Static table - hierarchical level 2 */
[RegistrationTable]
project_id (PARENT ID), registration_id (KEY, AUTO INC), registration_date

/* Dynamic table - hierarchical level 3 */
[RegistrationSubTable_xxxxxxxx]
registration_id (PARENT ID), firstname, lastname, email

/*Dynamic table - hierarchical level 3 */
[TRegistrationSubTable_yyyyyyyy]
registration_id (PARENT ID), book_title

(I hope this is understandable :)

My concern is that the number of dynamic tables will grow indefinitely, reaching tens of thousands in a few years. Will this present a problem for SQL (Currently MS SQL)?

Are there any caveats I haven't thought of?

Mongolojdo
  • 98
  • 6
  • 5
    Gut instinct says this is an incredibly bad idea. Why can't the primary key for your tables be extended to cater for individual forms, leaving you with a handful of tables? – Adrian Wragg Jul 19 '13 at 14:46
  • I have to agree with @AdrianWragg – Dev N00B Jul 19 '13 at 14:48
  • Take a look at [this answer](http://stackoverflow.com/a/5858666/1114171) – T I Jul 19 '13 at 14:49
  • @AdrianWragg, I don't know before hand, what kind of columns that will be required for each sub form. The columns will differ, not only by datatype, but also in type of content. I don't understand how this could be accomplished with a handful of tables. Could you please explain? – Mongolojdo Jul 19 '13 at 14:57
  • @TI: That approach was my first thought. The problem (at least I thought) was that it broked a normalization rule, stating that a column always should store a single type of CONTENT. In the approach you've linked to, you have a single column storing a single type of DATATYPE. That design will lead to problems later on when you build JOINS and stuff. – Mongolojdo Jul 19 '13 at 15:03
  • @Mongolojdo it's imo the better of two evils. with the approach you have outlined how would a user change a subform and what effect would that have on other tables etc. you'd have to consider that eventually you will hit the limit on the number of tables, what happens then... in the long run a design akin to that linked is going to be much easier to administrate, more scallable etc. if you are concerned about returning the correct datatype to the application then i'm pretty sure you could quite easily abstract this behind a proc with some dynamic sql – T I Jul 19 '13 at 15:11
  • @TI I have the code for updating the tables to reflect the form, done already. I am safe from the maximum number of tables by far. The beauty of my design (I hope, thus my question) is that it is fast. There will be a lot of tables, yes, but each table will contain only the number of registrations per project <10k. In the design you purpose, the number of rows will easily go > millions. The work of tracking the tables, columns and building queries is done dynamically by the application. – Mongolojdo Jul 19 '13 at 15:41
  • @Mongolojdo I personally am not in great favour of this design but am thinking it may be a good fit for using a NoSQL OO database or similar. – T I Jul 19 '13 at 15:51
  • @TI Yes, you may be right. In fact, the reason for this architecture is that I'm migrating from my old application build in (kill me...) Lotus Domino, where this approach where more natural. But so far, I have found more pros then cons about this architecture in SQL. In fact, it performs really well! I haven't tested it live yet and I dont want to ruin into problems in 5 years from now. (As I did with Lotus Domino. It's a terrible system) – Mongolojdo Jul 19 '13 at 16:04

1 Answers1

0

I find that this topic maximum-number-of-workable-tables answers my question.

In short, it will not cause a performance hit to have >10k of tables, not even millions. It is better for performance to have many small tables then to have few huge table. The problem is to query and manage them. In my case, this is not a problem as my application is doing that for me already.

Community
  • 1
  • 1
Mongolojdo
  • 98
  • 6