CODEWITHSUNDEEP
mysqldatabase
osman
osman

Reputation: 2459

how to design this database

  1. I have a table for users. Each user has certain skills they teach. So for example:

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

This is how I've done 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 then display "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 the right way to go about it - both in terms of designing the tables and querying (mysql)?

  1. Then say I want to update their profile with the areas they teach in:

    • Bob can teach karate in London
    • Louise can teach piano in Bolton and knitting in Manchester
    • Roger can teach judo in London and Manchester, sailing in Liverpool and fencing in Bradford

So I add the following tables:

Table cities
pk: city_id, city

1 London,
2 Manchester,
3 Liverpool,
4 Bolton,
5 Bradford,

But I'm confused as to how to do the relationships. I keep writing it out and realizing it doesnt work and starting again so I've obviously gone wrong somewhere.

Upvotes: 4

Views: 104

Answers (4)

Kalpesh
Kalpesh

Reputation: 5695

Yes, carry on with it. You should also add one more column in table user_skill which will hold city_id.

Upvotes: 0

Aniket
Aniket

Reputation: 379

Your Structure looks fine except your usr_skill table. To incorporate the last part add a fk city_id in user_skill table. If the player can teach the same skill in multiple cities, you will need an additional table to avoid multi-valued columns.

Upvotes: 0

Marc B
Marc B

Reputation: 360882

Can users teach skills in more than one location, e.g. "bob teaches judo in london and bolton"? Or is it strictly one skill-one city?

Depending on how you want your tables, you'd either just add a 'city' field to the user_skills table, and have multiple "bob/judo/cityX" "bob/judo/cityY" type records. Or you'll add yet another table "user_city_skills" where it'd be "user_skill_ID, cityID".

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71422

I would say your general DB structure is fine as far as the relations go. To incorporate the cities aspect you could use your proposed cities table, but also add a column to your user_skill table to include a reference to the city table.

Also make sure you use proper join statements in the select queries as this is best practice and helps the queries run as efficiently as possible.

Upvotes: 2

Related Questions