Menu
I've got a table of Users (ID, FNAME, LNAME, INTERESTS,...) plus another table to hold the specific set of INTERESTS they can choose from: Film, TV, Radio, Stage, Standup.
They can have more than one interest, so how can I store this information in the Users INTERESTS's field? Or what is the best alternative method to achieve this?
847
You can store the representation that is actually used in the application -- or really a serialized version of it. However, you get no SQL functionality from this, such as being able to count the number of coordinates or fetching rows that only have a certain name.
A SET is a string object that can have zero or more values, each of which must be chosen from a list of permitted values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas ( , ).
You can list a table's columns with the mysqlshow db_name tbl_name command. The DESCRIBE statement provides information similar to SHOW COLUMNS .
It's a many-to-many relationship. You store these by intoducing a "join table".
Table Users:
-----------
UserID PK
Name
...
Table Interests
-------
InterestID PK
Description.
....
User_interest
-----------
UserID PK, FK
InterestID PK, FK
Read about database normalisation in your MySQL book. It's an important topic, so there's probably a large chapter about it.
In short, you remove interests and end up instead with a third table, like:
user_id | interest_id
--------+------------
1 | 1
1 | 2
2 | 1
3 | 4
This gives you your many-to-many relationship.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
