Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In an application that allows users to define their own tables and columns (details of that are not the focus of this question, but imagine a tool that lets users create their own forms and automatically handles storage, just to get the idea) is it better to store each table and column they create in a "table" table and a "column" table, or to dynamically create tables and columns?

This "one table to rule them all" question is somewhat related, but in that case the "table" table is more clearly a bad idea. In this case, if you don't go that route then all your queries will have to be dynamically generated.

Even though the argument "don't write yet another application designer tool for non-programmers, so you can keep adding features until you are basically having users develop in a crummy version of, say, Visual Studio, using your custom crummy language" is pretty reasonable, I've had a lot of projects with some version of the user defined tables requirement and this question is about how to implement it. Whether to implement it is another question, but probably too specific to particular applications to be a good question here.

share|improve this question
    
I've been writing schemas for a decent while now, and I'm still unsure as to where this line should get drawn. Insightful answers wanted. –  TehShrike Jan 5 '12 at 8:49

5 Answers 5

up vote 0 down vote accepted

A generic database design limits the proliferation of new tables as the number of forms and data elements grows. On the other hand even the simplest query involved 3 tables, in our case.

Queries indeed got very mind boggling. After 3 years of this I became of the opinion that explicit tables was better. I'd rather deal with more tables that better reflect the business form/model - that I can understand just by looking at them; vice 1/2 the tables but with multitudes of meaningless join tables requiring astonishing long, nested queries to make even the most rudimentary sense out of the data. You actually start memorizing the meaning/context of individual identity column primary keys!

Finally, we had evolved to where we had that one table to rule them all so as to get away from some of the inherent complexities (and bad coding and design decisions of the past) in the existing design and evolving user requirements. Nonetheless, we were certain we'd have problems as the table size went into the million+ row size.

share|improve this answer
    
Thanks for answering. Was your design to handle dynamic table creation, or just to limit the number of tables? Also, how did you end up with 1/2 as many tables - did you have multiple sets of 3 generic tables for groups of similar functionality? (In my case, it would be one set of generic tables for all dynamically created tables - or a bunch of dynamically created tables and dynamically generated SQL). Based on your experience, which way would you recommend I go? –  psr Jan 4 '12 at 22:49
    
The intent was to limit the growth of the DB schema and the stored procedures. But this advantage was outweighed by the down side. The sql to form business-meaningful stuff was daunting, <i>and very difficult to write, understand, change, and test</i>. To the extent that coding was poor (shit is the technical term I used) - and we had that in spades, maintenance was a headache. The generic DB just made it harder. Finally dealing directly with the data day-to-day was painful. –  radarbob Feb 13 '12 at 21:15

Pros of "table" table- Simpler to write queries, quicker development time, you probably shouldn't be dealing with complicated functionality anyway, since that would be added by programmers, not users.

Cons of "table" table- The database can't see your "real" tables so it can't validate, optimize, or index very well.

Cons of dynamic tables - Ugh, writing dynamic queries is a giant pain.

share|improve this answer
    

If the tables that your user will define are not related through FKs, then you may want to consider dealing with such 'user defined tables' as spreadsheet files (or spreadsheets). You can present them as a grid and store them as csv files or convert each to a table. This approach would make life easier for you the developer and for the user since the user will be familiar with spreadsheets (I assume). In the .NET world there are many tools that gives you that spreadsheet look and feel.

share|improve this answer
    
They are related to other tables in the DB and used in all sorts of reports throughout the application. Other tables would have foreign keys to them. So just pushing to an external application or format would not be helpful in our case (though of course you had no way to know that). –  psr Jan 4 '12 at 22:53

Personally I try to do such a good job of defining the data needs that user defined fields and tables are not necessary. If you haven't defined 98% of what they would need, in most cases that means you haven't done your job. There are few genuine needs for that kind of flexibility (lab tests come to mind where new ones with very differnt needs come up all the time).

In the event I need them, I would first choose have a table with 5 or 6 basic columns plus the client_id to join to other stuff thqat the client could define as meaning something to them. Queries would use the generic name and you would set up some way to actually atach a label to the column. You might even have one client-defined table for each major function, so you could join to the Order table on orderid and get the client_defined fields. ANd have five or six fileds available for use for the users, etc.

At least this way the queries are written up front. The con is that you only get so many client defined field, but honestly in most situations no one will define any or they will only do one or two unless you did a horrible job o nthe orginal design. It is my experience that users love to say they want that flexibility, but they hate to actually use it because it confuses them. Additonally every COTS program I ever used that was sold for it's flexibility was a nightmare to actually use and slower than slow, so I find that flexibility is vastly overrated.

My very last choice would be an EAV table as they are slow and difficult to query. If I really really needed that functionality because in fact many client defined things were not only possible but likely, I would consider using a nosql database for that part of the application. At least nosql are optimized to better use EAV structures. No reason they can't be combined with relational databases in the same application.

share|improve this answer
    
Every client that buys this product defines their own tables after they buy it. Everyone uses this - it's part of why they buy the product. It currently does use EAV, but the question is whether this is better than dynamically generating tables. As far as the NoSQL, besides the difficulty adding another moving part, I am dubious they really are optimized. At the physical storage level they are basically the same as relational DBs. So it's basically the same as dynamically creating tables, except it might be easier in not making you define a schema or dynamically generated SQL. –  psr Jan 5 '12 at 18:26

I would clearly want to take advantage of the DBMS I'm using. I would map the data types I offer to DBMS data types and dynamically create real 1 to 1 tables in the DBMS (probably with a system of table name prefixes according to the foreseeable needs). Then, you can have simplified table definitions somewhere (another table, maybe), which describe the tables using the subset of data types and/or SQL you chose to use. These simplified definitions would be used to describe the tables to the frontend application, create your forms, format the query results, etc. I would think the bigger the subset, the best your application can get, but also the more complicated. I'm really thinking this in term of an interface between what you offer to your users and the DBMS.

For performance and flexibility, I'd stay close to the DBMS. You probably don't really want to optimize the multi-table multi-select multi-constraint queries yourself and re-implement keys, constraints, etc. You can still provide apparently different features to the users. The hard part is thinking the architecture in a way that the code doesn't get too complicated to use/maintain and to do not decrease the subset of features too much.

In the end, it depends on the actual flexibility and performance requirements too.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.