Member Avatar for deepucando

Hi

i am trying to design a social networking site for my project.

i have to design the database for the social networking with user profile and friends contact list as like facebook and orkut.

can anyone help me to implement the database design with fast and accuracy retrieval of data from the database using the Index and Hash table techniques

regards
~Deepu~

  • 3 Contributors
  • 2 Replies
  • 130 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by metalix

Dani AI

Generated

For a social graph the canonical SQL pattern is a normalized many‑to‑many relationship rather than storing lists or flat files. That yields indexable lookups, easy constraints, and predictable performance as the site grows. touched on alternatives; those approaches trade queryability and concurrency for simplicity and will become painful at scale.

A compact starting schema:

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE friendships (
  user_id INT UNSIGNED NOT NULL,
  friend_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,    -- 0=requested, 1=accepted
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, friend_id),
  INDEX (friend_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (friend_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

Design notes and tradeoffs: represent an accepted friendship either as a single row (user_a, user_b) and query both columns, or insert two directional rows so lookups need only WHERE user_id = X. The two‑row approach simplifies indexed reads; the single‑row approach saves space but makes queries slightly more complex. Keep PRIMARY KEY/indexes that match the most common queries (friends list, pending requests).

Performance tips: InnoDB uses B‑tree indexes; HASH indexes are limited to MEMORY tables—avoid MEMORY for persistent social data (MySQL indexes, MEMORY engine). Use EXPLAIN to verify query plans (EXPLAIN). Add caching (Redis/Memcached) for hot friend lists, denormalize counts when needed, and consider a graph database for heavy multi‑hop traversals (graph DBs). Test with realistic data and iterate.

Recommended Answers

All 2 Replies

Member Avatar for gunnarflax

I'm pretty sure this topic doesn't belong in the php forum.

Member Avatar for metalix

well it is probably being done in php
how big is this SNS going to be?
be careful not to put heavy load on the mysql server.
you could probably use xml files for this.
check my post on your other post for a basic sql layout though. and add a friends column, where you can use an explode() to get all the friends usernames

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.