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:
Add a comment
|
1 Answer
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?
8 Comments
codecowboy
you are right, the lookup table is surplus to requirements. without that, does it not meet 1NF because of the duplication of title?
derobert
col_1, col_2, ... are the 1NF problem. See en.wikipedia.org/wiki/First_normal_form where their example is a telephone number.
derobert
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.
codecowboy
How about worksheet_columns containing id, worksheet_id, label and order? Then drop worksheet_types. This gets rid of col1, col2 etc.
derobert
What you have there is the EAV model, which may be appropriate depending on your data. Wikipedia has details: en.wikipedia.org/wiki/Entity-attribute-value_model
|