Member Avatar for lkrvk

hi, guys i have a problem here.
i have a form which creates a new table for each new user and that user will have a monthly update of his salary now im confused how to go about this. do i need to create a new table or how to go about.............................

  • 2 Contributors
  • 8 Replies
  • 196 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Creating one table per user will become unmanageable quickly. ’s direction—to keep users and salary records separate—is the right pattern. Map the form fields (name, email, phone, address) to a single users table and record every monthly payment in a related payments/salary-history table that references the user’s primary key.

Keep these practical rules in mind:

  • Use a single integer primary key for users (e.g., user_id) and a payments table keyed by payment_id with a user_id foreign key.
  • Store money with DECIMAL(10,2) (not FLOAT) to avoid rounding errors.
  • Keep a proper date column (DATE or DATETIME) for the payment; if you require exactly one record per month, store a normalized period (e.g., first day of the month) and enforce UNIQUE(user_id, period).
  • Make check_number a VARCHAR (it can contain letters or leading zeros). Index user_id for fast lookups and add a foreign key to enforce referential integrity.

Example queries (retrieve history, aggregate by month):

SELECT u.name, p.amount, p.payment_date, p.check_number
FROM users u
JOIN payments p ON p.user_id = u.user_id
WHERE u.user_id = ?
ORDER BY p.payment_date DESC;
SELECT DATE_FORMAT(p.period, '%Y-%m') AS month, SUM(p.amount) AS total
FROM payments p
WHERE p.user_id = ?
GROUP BY month
ORDER BY month DESC;

Operational tips for a beginner ():

  • Validate and sanitize form input; use prepared statements to avoid SQL injection.
  • Wrap related writes in transactions when inserting or updating both user and payment records.
  • Add created_at/updated_at and consider an audit table if payments may be corrected.
  • Use a GUI like phpMyAdmin or MySQL Workbench to inspect and test the schema.

Follow the normalized approach suggested by , but prefer DECIMAL for amounts, a DATE/period field for month logic, and indexes/constraints to keep data consistent and queries fast.

Recommended Answers

All 8 Replies

Member Avatar for smantscheff

It's utterly nonsense to create a table for each new user.

Member Avatar for lkrvk

then brother how to go about it

Member Avatar for lkrvk

name:
email:
phone:
address:

this is the form for the new user and the below is for salary update for every month of each user

amount: date: check number:

this is the format i want brother please help me im struck up

Member Avatar for smantscheff

Create one table for users, one table for salaries, and join them on the user ID.

create table users
( id integer not null primary key auto_increment
, name text
, email text
, phone text
, address text
);
create table salaries 
( id integer not null primary key auto_increment
, id_user integer not null
, year integer not null
, month integer not null
, salary float not null
, foreign key (id_user) references users (id)
);
Edited by smantscheff because: n/a
Member Avatar for lkrvk

thanks bro but i dint get the second part of it could u please detail it for me

Member Avatar for smantscheff

What is the second part? And did you ever consult a manual?

Member Avatar for lkrvk 
CREATE TABLE salaries
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, id_user INTEGER NOT NULL
, YEAR INTEGER NOT NULL
, MONTH INTEGER NOT NULL
, salary FLOAT NOT NULL
, FOREIGN key (id_user) REFERENCES users (id)
);

this part is a bit confusing whats the manual where is it i don no anything about it i am a newbie

Edited by lkrvk because: n/a
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.