How do I create an empty table with SQL?

Question

This isn't much of an issue with MySQL per-se.

The Full Story

I'm writing a very small PHP framework. It isn't like existing frameworks where they force you to use a certain methodology. It isn't either like a CMS framework. Trust me, I've seen Zend framework and I've used CMSes like Joomla and WordPress extensively, none of them come close to what I'm doing.

Introducing The Issues

I'm writing the Database abstraction part. You get class methods like ::table_exists() etc. It is designed in a way that people can easily add different database classes and use them instead (eg; mysql, mssql, oracle, flatfile...).

They simply need to write a class which satisfies a base abstract classes'.

The Real Issue

I'm writing the functionality for ::table_create(), but have one main problem: MySQL doesn't like empty tables (ie, without a column).

I have several proposed fixes:

• For each new table, create a commonly used column, such as 'id' (type=INT)
• For each new table, create a temp column which doesn't use any space as much as possible (perhaps a boolean column?)
• Somehow delay table creation until at least one column can be created

This approach is most certainly new, and I'd like to here some unbiased comments about it (anything on the lines of "but no one does it that way" won't do).

Cheers!

Answer 1

Well I would either go with option 1), Adding a generic ID column, which you might find you need anyway, or with option 3) Delaying the table creation. I'm assume after they call ::table_create() they will be calling table_add_col(), etc. So just delay creation until there is at least one column, OR until they actually try and use the table for the first time.

Answer 2

Your proposed fixes look quite good. But I would recommen them in a diffrent order. If you are able to delay the creation, tht's probably the best. My second favorite would be to have a table with only an ID, although you might be delete this column, if you want to create a many-to-many relations table with two foreign keys only.

Answer 3

last of your points.

its really very strange what you are doing here. creating tables on the fly? dynamically or something? well... whatever you are trying to accomplish. you should have a look at document/object oriented databases like couchdb http://couchdb.apache.org/ ! you can create a document and dynamically add whatever fields you want. those are the closest thing to your "columns"

but as you like it... your first attempt is ugly because it might lead to conflicts. the second attempt is clumsy. but if you do so create a col with uniqueprefix_random so you can delete it afterwards. but its well... i dunno what to say about that.

theird approach seems the only senseful!