Stuartjrose
asked on
Is there a way to use 1 table to define the colums of another table
iI would like to create a mysql database table that will contain 3 fields.
Name
Description
default value
I would then like the records of this table to be used to create the fields of another table.
so that if a record contained the following
name = x
description = y
default value z
and another record contains
name a
description b
default value c
then the fields of the otehr table would be x and a.
the 2nd table would update with a new field whenever a new record was added to table 1.
furthermore when a new record is created in table 2, field x would default to z if no other value was specified at record creation.
What is the best way to go abvout this?
Name
Description
default value
I would then like the records of this table to be used to create the fields of another table.
so that if a record contained the following
name = x
description = y
default value z
and another record contains
name a
description b
default value c
then the fields of the otehr table would be x and a.
the 2nd table would update with a new field whenever a new record was added to table 1.
furthermore when a new record is created in table 2, field x would default to z if no other value was specified at record creation.
What is the best way to go abvout this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for the help people.
To clarify the Question.
the idea is that table 2 will be used to represent an rpg character with fields for attributes, skills and skill values.
I want the fields for skills and skill values to be user customizable. i.e a user can add a skill to a modern day skillset that will not be usable in a medieval set.
So that a user can add a skill a description of the skill and set it's default value and that will then become available to that user for every character they create using that skillset (table). I've tried to abstract that as much as possible for this explanation.
this is a self set project based on my interests in order to teach myself more advanced sql than just a query for all records with a value =x. Even if it only teaches me that what i want is best done outside SQL.
thanks for teh help everyone.
To clarify the Question.
the idea is that table 2 will be used to represent an rpg character with fields for attributes, skills and skill values.
I want the fields for skills and skill values to be user customizable. i.e a user can add a skill to a modern day skillset that will not be usable in a medieval set.
So that a user can add a skill a description of the skill and set it's default value and that will then become available to that user for every character they create using that skillset (table). I've tried to abstract that as much as possible for this explanation.
this is a self set project based on my interests in order to teach myself more advanced sql than just a query for all records with a value =x. Even if it only teaches me that what i want is best done outside SQL.
thanks for teh help everyone.
Can you describe the original table (i.e. provide column names and datatypes) with some sample data, then indicate what the structure and values should be in the other table?
To go with what you posted, you would probably have to use triggers to make this work. You would need to create an insert trigger on table 1 so that any insertion into that table would execute an alter statement on the second table to add the new column.
Likewise, you would want to create an insert trigger on table 2 to look at each column such that any data inserted into table 2 would execute a trigger on an empty column that then does a select from table 1 to get what the default value should be.
The second trigger may be a little complicated since it needs to look for all fields in the table that have an empty or null value and then do a select from table 1 to get each default value.