help to link values

User, date Message
User: doinkxs
Posted: Wed, 07 Jul 10 23:06
Category: General
4 posts since Tue, 06 Jul 10 		I am creating lots of same columns in diferent tables, that has some values (v1, v2, v3). I am thinking to use ENUM or SET column type, but if, in the future, I will have to add 'v4' to all the columns, I do not want to do it manualy in Lenght/Set column, in 30 tables. Is there any solution to have table with values for ENUM type of column? I am quite newbie in this, maybe I am missing something? Thanks.
User: anse
Posted: Wed, 07 Jul 10 23:56
1822 posts since Fri, 07 Apr 06 		As soon as you have more than one table with a column referring to some fixed set of values you should create a lookup table which holds:

id | name
1 | value1
2 | value2
3 | value3
...


In your 30 tables you just create a numeric column which is sufficient to hold the ids from that lookup table. Now you store just numbers instead of the values itself in your 30 tables. If you on one fine day need another value you just insert a new row into your lookup table.
User: doinkxs
Posted: Thu, 08 Jul 10 20:04
4 posts since Tue, 06 Jul 10 		Thank you anse,

I was talking exactly about it. But I was not able to do it myself, can you guide me how can I create lookup table and how can I define wich lookup table I should use (in case I have more than one)
User: anse
Posted: Thu, 08 Jul 10 21:50
1822 posts since Fri, 07 Apr 06 		Well you can use HeidiSQL to create your table:
- Rightclick the database in which the lookup table should reside
- Click "Create new" > "Table"
- Give it a name
- Add two columns called "id" and "name"
- "id" should be of type INT, length:10, unsigned, not NULL, Default: Auto-increment
- "name" should be of type VARCHAR, length: 100, not NULL
- rightclick "id": "Create new index" > "Primary"
- click "Save"
 

Please login to leave a reply, or register at first.