distal-attribute
distal-attribute
distal-attribute
distal-attribute

help to link values

User, date Message
Written by doinkxs
4 years ago
Category: General
5 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.
Written by ansgar
4 years ago
4936 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.
Written by doinkxs
4 years ago
5 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)
Written by ansgar
4 years ago
4936 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.