How to create this database

Question

• I have a table for users. Each user has certain skills that they teach. For example:

  • Bob can teach karate
  • Louise can teach piano and knitting.
  • Roger can teach judo, sailing and fencing.

Here is how I did it in the database:

Table users pk: uid, name 1 Bob, 2 Louise, 3 Roger Table skills pk: sk_id, skill 1 karate, 2 piano, 3 knitting, 4 judo, 5 sailing, 6 fencing Table user_skill (relationship between user and skills) pk:usk_id, fk:uid, sk_id 1 1 1, 2 2 2, 3 2 3, 4 3 4, 5 3 5, 6 3 6, 

I want to show "Roger has these skills: judo, basketweaving"

 select name, skill from users, skills, user_skill where users.uid = user_skill.uid and users.uid = 3 

Is this done correctly - as in terms of designing tables and queries (mysql)?

• Then say that I want to update my profile with areas in which they learn:

  • Bob can teach karate in London.
  • Louise can teach piano in Bolton and knit in Manchester.
  • Roger can teach judo in London and Manchester, swim in Liverpool and fencing in Bradford.

So, I am adding the following tables:

 Table cities pk: city_id, city 1 London, 2 Manchester, 3 Liverpool, 4 Bolton, 5 Bradford, 

But I am confused as to how to make a relationship. I continue to write and realize that it does not work and does not start again, so I obviously made a mistake.

Answer 1

I would say that your overall database structure is fine as far as relationships are concerned. To include the city aspect, you can use the table of suggested cities, but also add a column to the user_skill table to include a link to the city table.

Also make sure that you use the correct join operators in the selected queries, as this is best practice and helps the queries work as efficiently as possible.

Answer 2

Can users teach skills in several places, for example. "Bob teaches judo in London and the bolton?" Or is it strictly one master, one city?

Depending on how you want to use your tables, you simply add the "city" field to the user_skills table and have several entries like "bob / judo / cityX" "bob / judo / cityY". Or you add another table "user_city_skills" where there will be "user_skill_ID, cityID".

Answer 3

Your structure looks great except for the usr_skill table. To include the last part, add fk city_id to the user_skill table. If a player can teach the same skill in several cities, you will need an extra table to avoid multi-valued columns.

Answer 4

Yes, continue with this. You should also add another column to the user_skill table that will contain city_id .