I would like to start a simple wikepedia database system using postgreSQL with the following,

There are three tables users, categories and articles. In which users and articles are one-to-may through users id primary key. While categories is also one-to-may with articles by categories primary key. Please help me with the schema.

DROP DATABASE IF EXISTS wiki_db;
CREATE DATABASE wiki_db;


--- CREATE SCHEMA
-- CREATE SCHEMA wiki;

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL
);

CREATE TABLE categories (
  category_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE articles (
  article_id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  --- user_id has to be defined
  user_id INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP,
  -- category_id has to be defined
  category_id INTEGER NOT NULL,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
  CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
);

• seedings

SET search_path TO wiki;
-- Insert sample users
INSERT INTO users (name, email, password)
VALUES
  ('John Doe', 'john.doe@example.com', 'password1'),
  ('Jane Smith', 'jane.smith@example.com', 'password2'),
  ('Bob Johnson', 'bob.johnson@example.com', 'password3');

-- Insert sample categories
INSERT INTO categories (name)
VALUES
  ('Technology'),
  ('Sports'),
  ('Politics');

-- Insert sample articles
INSERT INTO articles (title, content, user_id, category_id)
VALUES
  ('The Latest Technology Trends', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 1, 1),
  ('The Top 10 Sports Moments of the Year', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 2, 2),
  ('The Latest Political Developments', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 3, 3);

          +----------+        +------------+
          | users    |        | articles   |
          +----------+        +------------+
          | id       |------->| id         |
          | username |        | title      |
          | email    |        | content    |
          | password |        | created_at |
          +----------+        | user_id    |
                              +------------+

          +-----------+        +------------+
          | categories|        | articles   |
          +-----------+        +------------+
          | id        |------> | id         |
          | name      |        | title      |
          +-----------+        | content    |
                               | created_at |
                               | user_id    |
                               +------------+

          +---------------------+  
          | categories_articles |    
          +---------------------+  
          | category_id         |
          | article_id          |
          +---------------------+       

• seeds it,

-- Insert sample users
INSERT INTO users (name, email, password)
VALUES
  ('John Doe', 'john.doe@example.com', 'password1'),
  ('Jane Smith', 'jane.smith@example.com', 'password2'),
  ('Bob Johnson', 'bob.johnson@example.com', 'password3');

-- Insert sample categories
INSERT INTO categories (name)
VALUES
  ('Technology'),
  ('Sports'),
  ('Politics');

-- Insert sample articles
INSERT INTO articles (title, content, user_id, category_id)
VALUES
  ('The Latest Technology Trends', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 1, 1),
  ('The Top 10 Sports Moments of the Year', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 2, 2),
  ('The Latest Political Developments', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.', 3, 3);

---

• To retrieve all articles written by a particular user, you can use a query like:

SELECT * FROM articles WHERE user_id = <user_id>;

• To retrieve all articles in a particular category, you can use a query like:

SELECT * FROM articles WHERE category_id = <category_id>;

• You can also join the "users" and "categories" tables with the "articles" table to retrieve additional information about the authors and categories of the articles, like so:

SELECT articles.title, articles.content, users.name AS author, categories.name AS category
FROM articles
JOIN users ON articles.user_id = users.user_id
JOIN categories ON articles.category_id = categories.category_id;

---

1. To use the categories_articles table in SQL, you can use SQL statements to insert, update, and delete rows in the table. Here are a few examples:
   • To insert a row into the categories_articles table linking an article with ID 1 to a category with ID 2:

INSERT INTO categories_articles (category_id, article_id) VALUES (2, 1);

2. To update the category of an article in the categories_articles table, assuming the article has ID 1 and you want to change its category to the one with ID 3:

UPDATE categories_articles SET category_id = 3 WHERE article_id = 1;

3. To delete a row from the categories_articles table linking an article with ID 1 to a category with ID 2:

DELETE FROM categories_articles WHERE category_id = 2 AND article_id = 1;

Note that when you insert or update a row in the categories_articles table, you need to make sure that the category_id and article_id values you are inserting or updating correspond to valid categories and articles that exist in the categories and articles tables.

4. You may want to use SQL joins to select data from these tables, as well as the categories_articles table, based on certain criteria. For example, to select all articles that belong to a certain category, you could use a query like this:

SELECT articles.title, articles.content
FROM articles
JOIN categories_articles ON articles.id = categories_articles.article_id
WHERE categories_articles.category_id = 2;

This query selects the title and content columns from the articles table, and joins it with the categories_articles table on the id and article_id columns. The WHERE clause filters the results to only include articles that belong to the category with ID 2.

Return to Top