CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-databasepostgresql
Luke101
Luke101

Reputation: 65268

Database design to create tables on the fly

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

Upvotes: 1

Views: 3600

Answers (5)

mangas
mangas

Reputation: 474

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

Upvotes: 2

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 1

Ben Burns
Ben Burns

Reputation: 15216

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.

Upvotes: 8

Justin Cave
Justin Cave

Reputation: 231711

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.

Upvotes: 17

Bogdan Rybak
Bogdan Rybak

Reputation: 2117

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

Upvotes: 2

Related Questions