CODEWITHSUNDEEP
phpmysqldatabase-designjoin
Wolfpack'08
Wolfpack'08

Reputation: 4128

Content-by-user: two tables with columns of the same name or join?

The title pretty much says it all. I have users posting content, and I would like to attach the user ID that is in the users table to the posts. Should I add a userid column in the posts table, should I join via the DB language, or should I use the scripting language to poll each table? If I should join, what kind of join should I do?

Sample content would look like this: "The teddy bears at my store are softer than the teddy goats, but the teddy goats are more lovable." - Posted by James Teddyman at 3:36 PM.

Upvotes: 2

Views: 115

Answers (3)

bos
bos

Reputation: 6555

A very often-used concept in situation like this is by having a users table and a post table, linking them together with a unique identifier. This identifier can be anything - a serialized id, a user name, mail address, etc - as long as it's unique. Linking is done using a foreign key constraint. Exactly how this is achieved in MySQL I do not know, but in Postgres it's done like this:

CREATE TABLE users (
  id serial PRIMARY KEY,
  name text
);

CREATE TABLE posts (
  content text,
  user_id integer REFERENCES users(id) NOT NULL
);

The tables are then merged using a join. This can be done in several ways, but here is a cross join after insertion of some values to play with:

@> INSERT INTO users (name) VALUES ('James');
@> INSERT INTO users (name) VALUES ('Jones');
@> INSERT INTO posts (content, user_id) VALUES ('Hello from James.', 1);
@> INSERT INTO posts (content, user_id) VALUES ('Greetings from Jones.', 2);
@> SELECT U.id AS user_id, U.name, P.content \
   FROM users U, posts P \
   WHERE U.id = P.user_id;

 user_id | name  |        content
---------+-------+-----------------------
       1 | James | Hello from James.
       2 | Jones | Greetings from Jones.

YMMV in MySQL, but I think the constructions above will work straight off.

(edit: Added INSERTs for clarification)

Upvotes: 5

Awais Qarni
Awais Qarni

Reputation: 18016

The pretty much way is to add a user id in the post table. Insert user id which each post. Then by extracting the information use the simple join to get the complete information. You select query should looks like

 select post.content,post.date,users.firstname,user.lastname from post,users where post.userid=users.id

Upvotes: 2

designosis
designosis

Reputation: 5263

Database Normalization would suggest that the best method is "two tables with columns", relating user content to user via user_id. This method is known to manage both bears and goats more efficiently in the long-run.

Upvotes: 1

Related Questions