help to link values

doinkxs posted 7 years ago in General
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.
ansgar posted 7 years ago
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.
doinkxs posted 7 years ago
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)
ansgar posted 7 years ago
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.