Four Thousand Rows in One Table

All four complaints have one root cause: a single table stores facts about three different things - customers, items and bills - in the same row. Any change to one fact must be repeated wherever that row appears.

Update anomaly (phone number). The customer's phone is stored once per bill line. Her phone number is a fact about the customer, but it is repeated in every line of every bill she ever had. Changing it means editing dozens of rows; missing one leaves contradictory data, and no query can tell you which copy is correct.

Insert anomaly (repeated bill details). A three-item bill repeats name and phone three times, so the same bill cannot be recorded without duplicating customer data. Worse, a new customer cannot be added at all until they buy something, and a new item cannot be listed until it is sold - the table forces unrelated facts together.

Delete anomaly (₹18,400). Deleting the rows for a customer who moved away removed the only record of those bills. Revenue history and customer history were welded to the same rows, so removing one destroyed the other. The correct behaviour is to mark the customer inactive and keep the bills.

Typo problem. 'Gel Pen', 'gel pen' and 'Gelpen' are three text values with no controlling list. Free text in a category column means the report groups by spelling, not by product. A lookup table of item codes with one canonical category fixes it, but only after the existing spellings are cleaned and mapped.

Proposed structure.

• Customers (CustID PK, CustName, CustPhone, CustAddress, City, ActiveFlag)
• Items (ItemID PK, ItemName, Category, UnitRate)
• Salesmen (SalesmanID PK, SalesmanName, SalesmanPhone)
• Bills (BillNo PK, BillDate, CustID FK → Customers, SalesmanID FK → Salesmen)
• BillItems (BillNo + ItemID composite PK, Qty, Rate, Amount; BillNo FK → Bills, ItemID FK → Items)

Why these keys.

• Surrogate keys (CustID, ItemID, SalesmanID) are short, stable, numeric and never edited. A phone number seems unique, but a customer can share a landline, change a number, or leave it blank.
• BillNo is naturally unique per bill and is a genuine business key, so it can serve as the primary key.
• BillItems needs a composite key of BillNo + ItemID: one bill has many items, one item appears on many bills, so neither column alone is unique. If the same item can legitimately appear twice on a bill (two rates, or a correction line), add a LineNo and make the key BillNo + LineNo.

Why not CustName or ItemName. Names are not unique (two customers may share a name), can change, may be misspelled or entered with trailing spaces, and are long text - every foreign key copy would carry the whole string. That is precisely how 'gel pen' and 'Gel Pen' became separate categories. A numeric ItemID makes the typo impossible, because the text name lives in exactly one row.

Migration. Load the four years of existing rows into a staging table, derive distinct customers and items, assign IDs, then insert into the new tables. Keep Amount stored as the line amount at the time of sale, because a later rate change must not alter historical revenue.

The query.

SELECT i.Category, SUM(bi.Amount) AS TotalSales
FROM BillItems bi
JOIN Bills b   ON bi.BillNo = b.BillNo
JOIN Items i   ON bi.ItemID = i.ItemID
WHERE b.BillDate >= '2025-01-01'
  AND b.BillDate <  '2025-02-01'
GROUP BY i.Category
HAVING SUM(bi.Amount) > 5000
ORDER BY TotalSales DESC;

Why each clause is there.

• JOINs reassemble the bill from its parts: BillItems holds the lines, Bills holds the date, Items holds the category. In the old flat table all three sat in one row, which is why repetition was necessary.
• WHERE with a half-open date range (>= 1 Jan AND < 1 Feb) is safer than BETWEEN on a datetime column, where a time of 18:30 on 31 January would otherwise be lost. Dates are written in ISO form to avoid ambiguity.
• GROUP BY i.Category is mandatory: every non-aggregated column in the SELECT list must be grouped, which is why selecting CustName here without grouping it would error or return arbitrary values.
• HAVING vs WHERE: WHERE filters rows before grouping (dates), HAVING filters groups after aggregation (a sum). Putting SUM(...) > 5000 in WHERE is a syntax error.
• ORDER BY the alias sorts the output; SQLite and MySQL allow the alias here, and most engines do too.

Why the typo problem disappears. The query groups on Items.Category, which exists once per item and is entered once. The same item cannot appear under two spellings, because there is one row for it and every sale points to it by ItemID. In the old flat table, grouping on free-text ItemName grouped by spelling, so 'gel pen' and 'Gel Pen' became separate groups and each total was understated. Note the limit: this only works after the existing spellings are cleaned once and mapped to canonical item rows - a database cannot repair text it has already been fed.