CODEWITHSUNDEEP
mysqldatabasedatabase-design
Dev Newb
Dev Newb

Reputation: 565

Database Design Flaw

Im having a hard time wrapping my head around a design flaw and I'm really hoping the community can help me out. My current design is:

SUBMISSIONS table

submission_ID (pk, int)
company_id (fk, int)
product_id (fk, int)
vendor_id (fk, int)
category (VARCHAR)
price (INT)
approval_status (TINYINT)
notes (MEDIUMTEXT)

VENDOR table

vendor_ID (pk, int)
vendor_name (VARCHAR)    

COMPANY table

company_id (pk, int)
company_name (VARCHAR)

PRODUCTS table

product_id (pk, int)
product_name(VARCHAR)

My Project is vendors submitting products to companies for review. The companies have a dashboard that contains a grid that pulls up all products submitted by vendors to them, they review them, then either approve or deny the products for use.

My issue is this...When pulling up the pending products grid I am grouping the submissions by their category. This way if there are 100 products in a category(which is common), they only have to see the category, not the individual products. They can enter notes on the group of submissions in a modal window that pops up from the grid. The id of the window is the first submission in the group and that submission is where the notes are stored. My major concern is that they can approve or deny submissions from the group individually so what happens when they enter notes for the group which is attached to the first submission in the group and then they deny that first submission. Now when they log in again all the notes for the group will seem to be gone because the notes are attached to that first submission which now sits in their denied products folder. There must be a better way of tracking notes to the group originally and then making those notes available to the individual submissions later but I'm not seeing it with my limited database design skills. Any advice at this point is helpful.

Upvotes: 1

Views: 163

Answers (2)

TheGeekYouNeed
TheGeekYouNeed

Reputation: 7539

As already mentioned, you should create a table for Categories, and a Submission would store a CategoryID.

You are executing a query to retrieve all of the items displayed in your modal. When you update Submissions in the group, you must also update all of the records.

So basically your update statement would be:

UPDATE Submissions
SET notes = @notes, approval_status = @approval_status
WHERE (same criteria from your select statement)

Since you are already narrowing down what you display in the modal, I assume you know how to send values to parameters to a SQL statement.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65274

My suggestion is

  • CREATE TABLE categories (id int AUTO_INCREMENT PRIMARY KEY, name VARCHAR(250) not NULL, UNIQUE INDEX(name), notes MEDIUMEXT)
  • replace category (VARCHAR) with category (fk, int) in SUBMISSIONS
  • drop notes (MEDIUMTEXT) in SUBMISSIONS
  • Now attach notes to category table

Upvotes: 1

Related Questions