Let's say I have a simple table containing two columns: id (auto_incremented) and type.
Now I have another table, with 10 fields. I need to add a field to show what 'type's this person has.
The person can have more than one type.
I can think of 4 methods to use here:
1) Add a boolean field per possible type in my bigger table
2) Add one field in the bigger table that is of type 'set'
3) Have a table with id and a boolean field for each possible type, and insert rows to cover each possible combination. Add field in my bigger table which will contain this id of the right combination.
4) Have a table with primary key of my big table and id fo type. Each person may have upto x rows in this table, where x is the number of types they have.
Which would be the best performance-wise and following good sql normalization/structuring? Let's assume some things to see a better picture:
- The large table has 1 million rows, getting many selects using WHEREs on the type (among other things).
- There are 5 different types
Now personally, I would definately eliminate number 1, no questions asked.
The other 3 options however, all seem to have their advantages and disadvantages.
Which one would you use, why....or offer a better method
PS: I'm currently using method number 2, and using FIND_IN_SET when doing my selects. Having no problems, but always curious if things can be done better.