Is it bad practice to use lists in a column of a record?

[Nicholas Peyton]

the impression I got was that it is bad practice to use "lists" in a column of a record?
Or is that just the way I've mistakingly interpreted it? (A web search on the topic does indeed definitely say that you can't use an 'array').

Imagine, for example, I had a group of extracurricular school teachers who all specialised in a certain kind of extracurricular weekend activity, (Tennis, Badmington, Football, Dancing, Ballet) etc etc.

If I simply did this:

Teacher_ID Proficiencies
50051 Tennis
Badmington
Football
Dancing
Ballet

50052 Football

50053 Dancing
Ballet
Tennis

A) It wouldn't require nulls
B) It would require less code if I wanted to "search" for all teachers who were proficient in Tennis.
C) A DB administrator wouldn't be required to add extra columns if you got a really knowledgeable teacher who was into everything.

And the query code (to find all 'Ballet' teachers):
SELECT Staff_ID
FROM Teacher_Proficiencies
WHERE Proficiencies LIKE '%BALLET%';


That's why I am questioning if it's really bad practice. (Coz it strikes me that a simple list (which could simply be updated/added to or changed) when necessary would be easier for both DB and customer? (Or what am I missing)?

________________________________________

The alternatives would be: (And this [is] actually the way I am currently doing it -- it is just that I am second-guessing/questioning myself)

[Teacher_Proficiencies_Two]
Teacher_ID Proficiency1 Proficiency2 Proficiency3 Proficiency4
50051 Tennis Badmington Football Dancing
50052 Football null null null
50053 Dancing Ballet null null

INSERT INTO Teacher_Proficiencies
VALUES
(50051, 'Tennis', 'Badmington', 'Football', 'Dancing'),
(50052, 'Football'),
(50053, 'Hunting', null, null, null),
(50054, 'Archery', 'Pottery', null, null),
(50055, 'Pottery', null, null, null);


And the longer (less efficient) query code (to find all 'Ballet' teachers):
SELECT Staff_ID
FROM Teacher_Proficiencies
WHERE Proficiency1 = 'Ballet'
OR Proficiency2 = 'Ballet'
OR Proficiency3 = 'Ballet'
Or Proficiency4 = 'Ballet'
Or Proficiency5 = 'Ballet';

________________________________________

Do you see what I mean? Is it acceptable to use the first (top) method]? Or is that bad practice and the second method [Teacher_Proficiencies_Two] is always "usually" the best/proper way?

Another way, (according to my research) would be to name the columns themselves after the different proficiencies and use logic (0 or 1) along with the 'TINYINT' datatype (due to MySQL not having the boolean operator):

I.E:
Teacher_ID Tennis Badmington Football Dancing
50051 1 1 1 1
50052 0 0 1 0

But then a DB admin would be required to alter the table structure every time the School wanted to add a new activity -- at least in the second (middle) option, you could just add loads of columns (Proficiency1, Proficiency2, Proficiency3) etc and it would more than likely at least be enough until a next scheduled annual maintenance etc.


I hope you see what I mean? What would you recommend?

Thank you kindly,

Nick Peyton