I'm wondering if it is possible to return a result set with column names which are stored in a separate table. Is this possible or do I need a stored_procedure with variables. See link for mysql_dump and description of required resultset:
+1
A:
You'd have to use a stored procedure that'd generate SQL dynamically and then run it. Column names aren't really first-class data in SQL, so you can't do much anything with them. They are determined at query parse time, before executing the query or fetching any data.
I suggest doing it in your app instead. Just have your app display/save/whatever the correct names instead of the database column names.
PS: You are abusing the relational model horribly. Please very carefully consider if you really want that schema. Your schema fails the first normal form. And what is worksheet_type_lookup for? Doesn't worksheets tell you which type?
derobert
2009-08-15 09:40:34
you are right, the lookup table is surplus to requirements. without that, does it not meet 1NF because of the duplication of title?
codecowboy
2009-08-15 10:16:02
col_1, col_2, ... are the 1NF problem. See http://en.wikipedia.org/wiki/First_normal_form where their example is a telephone number.
derobert
2009-08-15 10:40:08
arguably, col_1, etc. don't even *have* an applicable domain. They have no meaning. Instead, you're trying to give them meaning through another table.
derobert
2009-08-15 10:42:33
Thanks for your replies. What would you recommend? a table such as worksheet_columns? What fields would that table contain and how would it relate to the worksheet table?
codecowboy
2009-08-15 12:14:09
How about worksheet_columns containing id, worksheet_id, label and order? Then drop worksheet_types. This gets rid of col1, col2 etc.
codecowboy
2009-08-15 12:39:47
revised schema - http://pastie.org/584947
codecowboy
2009-08-15 12:45:27
What you have there is the EAV model, which may be appropriate depending on your data. Wikipedia has details: http://en.wikipedia.org/wiki/Entity-attribute-value_model
derobert
2009-08-15 22:10:34
How about http://stackoverflow.com/questions/801475/sql-design-and-or-php-class-for-storing-dynamic-two-dimensional-arraysI would not need the row labels. If not, what would you recommend? A worksheet must be generic and be able to have n columns. A user must be able to create their own worksheets with their own column names. A future requirement will also be to rate an individual cell.
codecowboy
2009-08-23 10:23:10
801475 is creating data which is positional in the database, and its main problem is that the cells table will grow really quick, and probably become a performance bottleneck. I've got to wonder there if maybe some other storage would have been better than a SQL database.
derobert
2009-08-23 19:54:05