Member Avatar for kamilacbe

Hi,
I have a scenario like XYZ company is creating a User management System , which allows to purchase for different company , so based on the package the system provides options to save last three or five user password to check and the max is ten .

XYZ---| Total password count 10
Company1--------|-----Company 2
company 1 takes with 5 retrivals where the other takes 2 retrivals, please let me know how can I design the DB for the same.
My present deisgn what i have done i have master table with following fields

Master table => Company ID , COmpany Name,User ID , Number Of password count,
User table => User Id , Company ID,User Name , Password .
Password table => User ID ,Password1,......password10.
I want them to be design in better normalisation , Thanks.

  • 3 Contributors
  • 2 Replies
  • 369 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by rubberman

Dani AI

Generated

Short summary and recommended pattern (normalized, per-company policy)
As wants per-company history limits (package-controlled, max 10), model the policy separately from users and keep password history as one row per prior password. This makes checks and cleanup simple and lets each company set 0..10 without fixed columns. 's point about not storing raw passwords is the baseline; 's trigger idea is possible but has tradeoffs — see notes below.

Example table layout (MySQL-friendly)

CREATE TABLE companies (
  company_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  password_history_limit TINYINT NOT NULL DEFAULT 3
  -- enforce 0..10 in app or with CHECK if your MySQL version supports it
);

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  company_id INT NOT NULL,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(company_id)
);

CREATE TABLE password_history (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id, created_at),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Change/check workflow (app-side, in a single transaction)

  1. Read company.limit.
  2. Hash new password with your KDF (see security notes).
  3. Fetch last N hashes for user: SELECT password_hash FROM password_history WHERE user_id=? ORDER BY created_at DESC LIMIT N. If any match the new hash, reject.
  4. Move current users.password_hash into password_history and update users.password_hash to the new hash.
  5. Prune older rows beyond N (DELETE using a derived-subquery to keep the most recent N).

Security & operational notes

  • Use a modern, memory-hard KDF (argon2id preferred; bcrypt/scrypt acceptable). Store the full encoded hash so params/salt travel with it. Re-hash on login if params are outdated.
  • Do hashing and reuse checks in the application layer (simpler and easier to test). Triggers can enforce DB invariants (pruning) but cannot run external hashing libraries and can complicate migrations.
  • Run these steps inside a transaction to avoid races; index password_history(user_id, created_at) for performance.
  • Limit access to these tables, audit changes, and consider encryption-at-rest for sensitive systems.

Recommended Answers

All 2 Replies

Member Avatar for rproffitt

Alarm bells went off on this one. Try this. Don't store the actual password. Make a hash of the password so to avoid troubles down the road.

That's it.

Member Avatar for rubberman

You need a trigger that will run and update the password count and such when a user logs in or out.

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.