Member Avatar for stickdog

For the first time, I am developing in an environment in which there is a central repository for a number of different industry standard reference data tables and many different customers who need to select records from these industry standard reference data tables to fill in foreign key information for their customer specific records.

Because these industry standard reference files are utilized by all customers, I want to reserve Create/Update/Delete access to these records for global product administrators. However, I would like to implement a (semi-)automated interface by which specific customers could request record additions, deletions or modifications to any of the industry standard reference files that are shared among all customers.

I know I need something like a "data change request" table specifying:

user id,
user request datetime,
request type (insert, modify, delete),
a user entered text explanation of the change request,
the user request's current status (pending, declined, completed),
admin resolution datetime,
admin id,
an admin entered text description of the resolution,
etc.

What I can't figure out is how to elegantly handle the fact that these data change requests could apply to dozens of different tables with differing table column definitions. I would like to give the customer users making these data change requests a convenient way to enter their proposed record additions/modifications directly into CRUD screens that look very much like the reference table CRUD screens they don't have write/delete permissions for (with an additional text explanation and perhaps request priority field). I would also like to give the global admins a tool that allows them to view all the outstanding data change requests for the users they oversee sorted by date requested or user/date requested. Upon selecting a data change request record off the list, the admin would be directed to another CRUD screen that would be populated with the fields the customer users requested for the new/modified industry standard reference table record along with customer's text explanation, the request status and the text resolution explanation field. At this point the admin could accept/edit/reject the requested change and if accepted the affected industry standard reference file would be automatically updated with the appropriate fields and the data change request record's status, text resolution explanation and resolution datetime would all also be appropriately updated.

However, I want to keep the actual production reference tables as simple as possible and free from these extraneous and typically null customer change request fields. I'd also like the data change request file to aggregate all data change requests across all the reference tables yet somehow "point to" the specific reference table and primary key in question for modification & deletion requests or the specific reference table and associated customer user entered field values in question for record creation requests.

Does anybody have any ideas of how to design something like this effectively? Is there a cleaner, simpler way I am missing?

Thank you so much for reading.

  • 2 Contributors
  • 1 Reply
  • 121 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by terza

Dani AI

Generated

For : common, practical patterns that keep the production reference tables clean are to separate the request metadata from the proposed data (the payload) and to choose one of two payload models depending on how tightly you want DB-level validation.

One proven approach is typed staging: create shadow copies of each reference table in a separate schema and insert the user's proposed row there. A small, single “request header” row points to the shadow row. Admins review the shadow row in an identical CRUD screen, optionally edit, then run a single transactional apply that moves the typed row into the live table. This gives you full column typing, constraints and easier validation, at the cost of maintaining parallel schemas when the reference schema changes.

The other approach is a single request repository with a serialized payload (JSON/XML). Store the target table name and a document containing the proposed column values. Render the edit screens on the fly from metadata (information_schema or your own field-definitions). This centralizes requests and avoids schema duplication, but pushes most validation into application logic and makes referential integrity enforcement harder until you apply the change.

Practical tips that apply to either model: validate inputs early and run duplicate checks, capture a snapshot or checksum of the live row to detect conflicts at apply time, keep the apply step transactional and logged for auditability, avoid blind deletes by preferring soft-deletes or an approval stage that captures the entire deleted row, and provide admins a single apply/reject workflow that updates the request record atomically. A minimal transactional apply (pseudo-SQL) looks like:

BEGIN;
INSERT INTO prod.table (columns...) SELECT columns... FROM staging.table WHERE staging_id = :id;
UPDATE requests SET processed_flag = 1 WHERE request_id = :rid;
DELETE FROM staging.table WHERE staging_id = :id;
COMMIT;

Choosing staged-typed tables is simplest for strict data integrity. The serialized payload route is simpler to evolve. Both can meet the goal of keeping production tables free of request-related columns while giving customers familiar CRUD screens.

Member Avatar for terza

Im new in designing Database and I would like to design a database that will hold all contacts for clients including questionnaires on the templates. Is it possible? if so please help me do it.

Thank you.
Terza

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.