MySQL Forums
Forum List  »  Newbie

Re: Is it bad practice to use lists in a column of a record?
Posted by: Phillip Ward
Date: November 01, 2021 06:44AM

As always when thinking about storing data, ask yourself how you're going to access it.

Are you ever going to ask questions like "who teaches Ballet"?

If the answer is "Yes" - at it clearly is - then you have to arrange your data to optimise this access path.

Wildcard string searching (like '%BALLET%') is appallingly slow and cannot use Indexes (that leading wildcard makes that impossible) so you're looking a Table Scanning queries - Bad.

Having repeated fields (Proficiency1 .. 5) is also terrible, because you need five indexes instead of one (slow inserts and updates) and the SQL will drive you insane!

Do it the Relational Way:
Split the proficiencies out into a child table, linked back to the user:

select * fron Teacher_Proficiencies ; 

+-------+-------------+
| t_id  | proficiency |
+-------+-------------+
| 50051 | Tennis      |
| 50051 | Badmington  |
| 50051 | Football    |
| 50051 | Dancing     |
| 50052 | Football    |
| 50053 | Hunting     |
| 50054 | Archery     |
| 50054 | Pottery     |
| 50055 | Pottery     |
+-------+-------------+

In fact, you'd probably have a separate table that described each proficiency and only add the id of that record into this one.

This is a completely extensible model, allowing new Proficiencies to be added without structural database changes.

Now; finding Ballet teachers?

SELECT t_id
FROM Teacher_Proficiencies
WHERE Proficiency = 'Ballet' 
order by t_id ;

And, for the "esoteric" pastimes:

SELECT t_id
FROM Teacher_Proficiencies
WHERE Proficiency in ( 'Ballet', 'Archery' )
group by t_id 
having count(*) = 2 
order by t_id ;

Regards, Phill W.

Options: ReplyQuote


Subject
Written By
Posted
Re: Is it bad practice to use lists in a column of a record?
November 01, 2021 06:44AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.