CODEWITHSUNDEEP
phpmysql
BBDev
BBDev

Reputation: 93

How do I use multiple tables to create a user profile?

I made a firm to add a user to my database now I want to have two tables. One table keeps track of the languages the user knows and the other table the design software the user uses.

Would I create 3 tables (profile, languages, software) each with an I'd field and when I add a user add a row to each table?

Upvotes: 0

Views: 301

Answers (1)

chaseshak
chaseshak

Reputation: 313

As you begin to add several many-to-many relationships, you need more tables to 'link' the information together. Here's how I would tackle the problem:

Note The IDs should all be unique indexed columns. Consider using AUTO_INCREMENT.

Table 1: Contains user's profile information

| ProfileID |UserInfo   |
|=======================|
|     0     | Info      |
|-----------------------|
|     1     | Info2     |
|-----------------------|

Table 2: Stores the possible languages

|LanguageID |LanguageName|
|========================|
|     50    | Python     |
|------------------------|
|     51    | Java       |
|------------------------|

and so on...

Table 3: Stores the Profile links to the languages

|ProfileID  |LanguageID  |
|========================|
|     0     |     50     |
|------------------------|
|     0     |     51     |
|------------------------|
|     1     |     50     |
|------------------------|

Every time you wanted to add a language to a user's profile, you'd create an entry in this table.

You would add two more tables for the software a user knows. One table for all the possible types of software, and another to store the links.

When you want to retrieve the information, you would do an operation such as the one below:

SELECT * FROM Table3
LEFT JOIN Table2
ON Table3.LanguageID = Table2.LanguageID
WHERE ProfileID = [TheProfileIDToSearch]

This structure uses JOIN to link tables together to return information from several tables at once. Here is a W3Schools quick explanation about SQL JOINS.

Upvotes: 2

Related Questions