Is it possible to share a set between two tables in MySQL?

Question

I am currently in the process of designing a database.

I have a table of 20,000+ records, which has a set in it (4 values). I also am making another table (100+ records) which will have an enum over the same set (1 value from the same set)

Example of current:

tbl1                          tbl2
ID   | Letters | Stuff        ID   | Letter | Stuff 
----------------------        ---------------------
0    | A,B,C,D | ...          0    | D      | ...
1    | A,B,C,D |              1    | C      |
2    | C,D     |              2    | A      |
3    | B,C,D   |              3    | D      |
...

Is there a way to make sure that the sets are the same, and can I compare the enum and the set?

I also might need to add more options to the set as our data changes. Would a separate table for that set be necessary, and then an association table for that?

Example of what I just said:

tbl1                tbl2             
ID | Stuff          ID   | LetterID | Stuff 
------------        ------------------------  
0  | ...            0    | 3        | ...
1  |                1    | 2        |
2  |                2    | 0        |
3  |                3    | 3        |
...

tblLetters          tblLetters1 (Association table)
ID | Letter         tbl1Id | letterId
------------        ------------------
0  | A              0      | 0
1  | B              0      | 1
2  | C              0      | 2
3  | D              0      | 3
...?                ...

My only major concern with this is whether the size of the association table would be too big (most of the rows in tbl1 will have all 4 elements of the set).

Thank you! Sorry if I didn't explain my problem very well. I'm very green when it comes to SQL.

Answer 1

Your second solution seems fine, comma separated values in 1 column should normally be avoided. You might not need an ID, and I'd drop the ENUM type for the column, and use the actual type / column definition of the letter in tblLetters:

tbl1                tbl2             
ID | Stuff          ID   | Letter   | Stuff 
------------        ------------------------  
0  | ...            0    | D        | ...
1  |                1    | C        |
2  |                2    | A        |
3  |                3    | D        |

tblLetters          tblLetters1 (Association table)
Letter              tbl1Id | letter
------------        ------------------
A                   0      | A
B                   0      | B
C                   0      | C
D                   0      | D

Possibly add a FOREIGN KEY constraint to tblLetters1.letter & tbl2.letter to enforce an existing letter from tblLetters.

And 80K rows in total is not many by any standard, it should be fine (use the proper indexes though)

Answer 2

I'm going to take a stab at your question....
So from what I understand, you just want to make sure the tables have the "options" or "variables" in the enum and set fields.

What you can do is:

Show create table tbl1;  

What you should see is

Create table tbl1   
(id int unsigned,  
stuff set('A','B','C','D'),
.....)  

Show create table tbl2;  
Create table tbl2   
(id int unsigned,  
stuff enum('A','B','C','D'),
.....)  

All you would need to to, technically, is make sure both tables have the same variables. You can do this with a script or just be aware of it when you do an ALTER TABLE.