1

I need to create dynamic tables in the database on the fly. For example, in the database I will have tables named:

Table
Column
DataType
TextData
NumberData
DateTimedata
BitData

Here I can add a table in the table named table, then I can add all the columns to that table in the columns table and associate a datatype to each column.

Basically I want to create tables without actually creating a table in the database. Is this even possible? If so, can you direct me to the right place so I can research? Also, I would prefer sql server or any free database software.

Thanks

aymeric
  • 3,877
  • 2
  • 28
  • 42
Luke101
  • 63,072
  • 85
  • 231
  • 359
  • Do you know how it would handle 1000s of entries? Also, I have no idea what the columns are going to be. – Luke101 Aug 26 '12 at 05:25
  • Yes - it would copy thousands of entries to a temporary table, what is you real question – Adrian Cornish Aug 26 '12 at 05:26
  • This dynamic table will be loaded on a webpage that may contain 1000s and or hundreds of columns and entries. I am afraid it may take too long to perform. basically, I dont know a good database design for this kind of problem. – Luke101 Aug 26 '12 at 05:30
  • Do not optimize until it is slow - try it first. Copying a 1000 rows to an in memory table on a 3GHz machine is much faster then you think – Adrian Cornish Aug 26 '12 at 05:40
  • 3
    Luke, you are asking for help with a particular solution rather than help on how to solve the problem. Your solution is unquestionably flawed (no hard feelings) so you may want to post more info about the actual problem to get a better solution. – Brandon Moore Aug 26 '12 at 05:52
  • Ben burns hit it dead on. I am trying to create a database within a database. But I am not sure how to go about it. What database design should I follow, etc. – Luke101 Aug 26 '12 at 05:58
  • 3
    With PostgreSQL this could be efficiently implemented using the hstore data type and avoiding the EAV (anti)pattern. –  Aug 26 '12 at 07:09
  • First, database-within-a-database models are often a *bad* plan, it's sometimes referred to as "Enterprise-y" design. There are times you need it, but it's a problem more often than it's right. Consider asking in more detail about the *problem* you're having, not the proposed solution. +1 to @a_horse_with_no_name's suggestion of hstore; if you have to do this, hstore is often the sanest way. – Craig Ringer Aug 26 '12 at 10:36
  • As @ChrisShain said on the [repost of this question](http://stackoverflow.com/questions/12131443/database-within-a-database-table-design) this is classic [inner platform effect](http://en.wikipedia.org/wiki/Inner-platform_effect) as described by [TDWTF](http://thedailywtf.com/Articles/The_Inner-Platform_Effect.aspx), and is usually terrible design. Commenting here so people see it when they find this in searches. – Craig Ringer Aug 27 '12 at 02:17
  • @Luke101, maybe I wasn't clear in my answer. Like the others said, what you're trying to do is a bad idea. Your time will be much more well-spent learning your database's DDL and metadata schema so that you can write code which reacts to and automates changes to your database structure. Again, if you can talk more about the real-world problem that you're trying to solve, we can recommend a better approach. – Ben Burns Aug 27 '12 at 19:27

5 Answers5

17

What you are describing is an entity-attribute-value model (EAV). It is a very poor way to design a data model.

Although the data model is quite flexible, querying such a data model is quite complicated. You frequently end up having to self-join a table n times if you want to select or filter on n different attributes. That gets slow rather slow and becomes rather hard to optimize relatively quickly.

Plus, you generally end up building a lot of functionality that the database or your ORM would provide.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 3
    PostgreSQL's `hstore` feature provides a way to do this kinda-sorta-sanely. `hstore`s are basically string dictionaries, ie key/value maps, that're stored in database fields just like any other data type. See http://www.postgresql.org/docs/current/static/hstore.html . It's a heck of a lot better than EAV. – Craig Ringer Aug 26 '12 at 10:37
8

I'm not sure what the real problem you're having is, but the solution you proposed is the "database within a database" antipattern which makes so many people cringe.

Depending on how you're querying your data, if you were to structure things like you're planning, you'd either need a bunch of piece-wise queries which are joined in the middleware (slow) or one monster monolithic query (either slow or creates massive index bloat), if one is even possible.

If you must create tables on the fly, learn the CREATE TABLE ALTER TABLE and DROP TABLE DDL statements for the particular database engine you're using. Better yet, find an ORM that will do this for you. If your real problem is that you need to store unstructured data, check out MongoDB, Redis, or some of the other NoSQL variants.

My final advice is to write up the actual problem you're trying to solve as a separate question, and you'll probably learn a lot more.

Ben Burns
  • 14,978
  • 4
  • 35
  • 56
2

Doing this with documents might be easier. Perhaps you should look at a noSQL solution such as mongoDB.

Bogdan Rybak
  • 2,107
  • 1
  • 19
  • 22
  • 2
    And a bonus, MongoDB is web scale! – ta.speot.is Aug 26 '12 at 05:35
  • 2
    Eesh... I think if there's a variant of Godwin's law for database discussions, "web scale" would be the phrase that pays... – Ben Burns Aug 26 '12 at 05:38
  • 2
    @BenBurns: "web scale" also gives you high scores in "bullshit bingo" ;) –  Aug 26 '12 at 07:07
  • 2
    To be fair MongoDB can deal with tables with [1,000](http://stackoverflow.com/questions/12032962/fetching-inserting-huge-chunks-of-data-from-to-a-large-table#comment16064755_12032962) to [100k](http://stackoverflow.com/questions/11729820/is-this-query-irreducibly-complex#comment15565272_11729820) records, which MySQL can't. – Ben Aug 26 '12 at 08:04
  • @Ben 100k records shouldn't give *any* database trouble - even SQLite should (mostly) work with that. Of course, if that's 100k records EAV-exploded into 1 million and accessed via multiple self joins... not so much. – Craig Ringer Aug 26 '12 at 12:31
  • @CraigRinger, the links are to my responses to comments by people suggesting changing to a NoSQL solution because there were that many records in a table. – Ben Aug 26 '12 at 12:32
  • 1
    Sorry @CraigRinger, I don't think you quite understand. My comment was sarcastic. I'm well aware that a RDBMS works fine for a large number of records. I work with them and have many tables with records in the hundreds of millions. If you read the linked comments I say it's unnecessary to change to a NoSQL solution rather than a RDBMS because there's a 100k records in a table. – Ben Aug 26 '12 at 13:12
  • @Ben Ah, I hear you. I didn't see the linkified text, and without that misread it. – Craig Ringer Aug 26 '12 at 13:13
2

Or you can still create the Temporary tables but use a cronjob and create the Temporary tables every %% hours and rename it to the correct name after the query's are done. so your site is stil in the air

What you are trying to archive is not not bad but you must use it in the correct logic way.

*sorry for my bad english

mangas
  • 464
  • 4
  • 8
1

I did something like this in LedgerSMB. While we use EAV modelling for a few things (where the flexibility is needed and the sort of querying we are doing is straight-forward, for example menu nodes use this in part), in general, you want to stay away from this as much as possible.

A better approach is to do all of what you are doing except for the data columns. Then you can (shock of shocks) just create the tables. This gives you a catalog of what you have added so your app knows this (and you can diff from the system catalogs if you ever have to check!) but at the same time you get actual relational modelling.

What we did in LedgerSMB was to have stored procedures that would accept a table name exists ('extends_' || name supplied). If so would add a column with the datatype required and write this to the application catalogs. This gives us relational modelling of extended attributes. At load time, the application loads the application catalogs and writes queries as appropriate at appropriate points to load/save the data. It works pretty well, actually.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182