Mysql Cross Table Referencing - Is this the right way?
I have one table with USERS and another table with RSS FEED URLS. Both are InnoDB type.
Each user may be subscribed to any number of the feeds.
I'm trying to figure out the best way to keep record of which feeds users are subscribed to.
One way is to have a feed_ids varchar column in users table and separate each. Is there a better way or is this ok?
USERS TABLE
+---+----------+
|id | feed_ids |
+---+----------+
|1 | 1,2,3开发者_开发问答 |
+---+----------+
|2 | 2,3 |
+---+----------+
|3 | 1,2 |
+---+----------+
It's best to create a new entry in the table for each relation. So:
USERS-FEEDS TABLE
+---+----------+---------+
|id | user_id |feed_id |
+---+----------+---------+
|1 | 1 |1 |
+---+----------+---------+
|2 | 1 |2 |
+---+----------+---------+
|3 | 1 |3 |
+---+----------+---------+
|4 | 2 |2 |
+---+----------+---------+
|5 | 2 |3 |
+---+----------+---------+
|6 | 3 |1 |
+---+----------+---------+
|7 | 3 |2 |
+---+----------+---------+
You can also define the relation between this table and your users and feeds table, to ensure integrety (ie. the user column can only contain values which are ID's in the user-table, and will be updated, deleted or be set null if you allow that when the user row is updated or deleted).
精彩评论