Let's start with a simple database design for a reference system app. Here is a basic design that could be used as a starting point:

1. User table:
   • This table stores information about each user, including their username, email address, and password. Each user has a unique user ID.
2. Reference table:
   • This table stores information about each reference item, including the title, author, publication date, and other relevant details. Each reference item has a unique reference ID.
3. Collection table:
   • This table stores information about each collection of references, which can be used to group related references together. Each collection has a unique collection ID and a name.
4. Tag table:
   • This table stores information about each tag that can be used to categorize references. Each tag has a unique tag ID and a name.
5. Reference-Tag table:
   • This table serves as a link between the reference and tag tables, allowing multiple tags to be associated with each reference.

This simple database design could be expanded upon to include additional features, such as attachments, notes, and shared references, as needed. However, it provides a basic framework for storing and organizing reference information for a reference system app.

I would like to design a reference system using postgresql schema with users, references, collections, tags, reference-tag, attachments and notes, how to do it.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL
);

CREATE TABLE references (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  url VARCHAR(255),
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE collections (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE reference_tag (
  reference_id INTEGER NOT NULL REFERENCES references(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (reference_id, tag_id)
);

CREATE TABLE attachments (
  id SERIAL PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  url VARCHAR(255) NOT NULL,
  reference_id INTEGER NOT NULL REFERENCES references(id) ON DELETE CASCADE
);

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  reference_id INTEGER NOT NULL REFERENCES references(id) ON DELETE CASCADE
);