Best method for categories via php and mysql

Question

I am creating a system with php + mysql. It is a type of social network.

I am in the planning part of the database and with a doubt on the best way to record, for example, the skills of the individual.

Example:

Name: John Doe Age: 30 Skills: php, mysql, css

Each "skill" has a specific registration, with photo description etc. Therefore, I need the person to enter the 'page' of the skill in question and "short" or "follow" and then it appears in her profile.

I thought to keep these 'abilities' separated by comma in the person’s database. So, when it comes to listing, usage explodes() and I can even make each word a link that leads to a selection of people with the same skills etc... Until then, ok. But what about to register/edit/delete this data? I don’t want the person to write anything there, just add "like/follow"... and it’s getting complex to perform... Adding ",css" when the person following "css" is a good strategy, or has some more recommended method?

Answer 1

Use relationships between tables to your advantage to solve this problem.

Create a table for skills:

CREATE TABLE habilidades (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(256) NOT NULL,
    descricao TEXT NOT NULL
)

And any other information that habilidades possess, then relate the user to the skill.

CREATE TABLE usuario_habilidade (
    usuario int NOT NULL,
    habilidade int NOT NULL,
    PRIMARY KEY (usuario, habilidade),
    FOREIGN KEY (usuario) REFERENCES SuaTabelaDeUsuario(id)
    FOREIGN KEY (habilidade) REFERENCES habilidades(id)
);

That way, whenever you want to add a skill to the user, just add a record to the table usuario_habilidade.