0

Background

I am OK-ish at Database Design...and I am building out this layer first. It is for a KPI tracker app, for real estate investment businesses.

The app's business requirements

This KPI tracker manages marketing channels' trackable items that contain trackable item metrics, that may be managed by people in multiple roles of the same team.

Here's example set up:

Example set up

This is the set up that we'll be using for this Question...

Marketing channels:

  • Driving for Dollars
  • Pre-foreclosures
  • Probates

Trackable items:

  • Driving for Dollars:
    • Cold calling
    • Post cards
  • Pre-foreclosures
    • Cold calling
    • Post cards
  • Probates
    • Cold calling

The cold calling, forms the trackable item metrics set we'll be using in this Question. Let's say that we have 2 cold callers, Alice and Bob, who may have a supervisor different from the company owner that they report to.

Now back to the business requirements...

The trackable item metrics contains metrics that may be managed by several people... To manage this (pun maybe intended?!), we wish to establish a user permissions for each of the columns .

For example, they may be managed by someone on the cold calling team, an acquisition partner who has no part in that operation (e.g. to track the actual lead, appointment, and contract counts), a disposition partner (e.g. to track the gross income made off a deal)

What do the tables look like right now?

Like this:

permissions

| perm_id | perm_desc        |
|---------|------------------|
| -1      | admin            |
| 0       | moderate         |
| 2       | modify_quantity  |
| 3       | modify_cost      |
| 4       | modify_response_count |
| 5       | modify_lead_count   |
| 6       | modify_appointment_count    |
| 7       | modify_contract_count     |
| 8       | modify_gross_profit      |
|trackable_items|
|---------------|
|PK| ti_id | 
|FK| mc_id |
| | ti_name |
| | ti_amount_type |
|trackable_item_metrics|
|----------------------|
|PK| tim_id |
|FK| ti_id |
| | tim_name |
| | tim_quantity |
| | tim_cost |
| | tim_response_count |
| | tim_lead_count |
| | tim_appointment_count |
| | tim_contract_count |
| | tim_deal_count |
| | tim_gross_income |

Now, about that user_permissions table...

My attempt at it

I keep the following use cases in mind:

  • Alice logs in, to report the results of a cold calling session
  • Company owner logs in, to report on the leads that he spoke to and converted from Alice's efforts

as motivation for this rough-draft attempt:

| user_permissions |
|--------------------|
|PK| up_id |
|FK| user_id |
|FK| permission_id |
|FK| ti_id |
|FK| tim_id |

This would allow an individual cold caller, who may have permission to set certain trackable_item_metrics across different trackable_items, and for someone (e.g. the manager, acquisition partner, disposition partner, or company owner) to set across entire trackable_items.

Right away, I see glaring problem with this:

that we have both ti_id (which is from trackable_items table) and tim_id (which is from child table trackable_item_metrics). This invites an opportunity to break referential integrity. Let's say that we have entries in the trackable_item_metrics table:

| tim_id | ti_id | ... |
| ---------------------|
|  99 | 21 | ... |
| 102 | 23 | ... |
| 103 | 23 | ... |

and we want to log some permissions for the trackable item metric where tim_id = 102...

We would write a row in the user_permissions having the tim_id and ti_id of 102, 23 respectively...

...but WHAT STOPS SOMEONE FROM WRITING tim_id = 102, ti_id = 21 ?!

HELP PLZ

Unfortunately, right now, I don't have solution to this...except to somehow hash the two id's together, to reference, and we check that hash on any update attempt.

What approach should I take on this?

What I have decided to do for now...

First create Stored Procedure:

CREATE DEFINER=`miwarren`@`%` PROCEDURE `CheckUserPermissions`(IN _ti_id INT, IN _tim_id INT)
BEGIN
    DECLARE valid_condition BOOLEAN;

    SELECT EXISTS(
        SELECT 1 FROM trackable_item_metrics
        WHERE ti_id = _ti_id AND tim_id = _tim_id
    ) OR
    (_tim_id = -1) OR
    (_ti_id = -1 AND _tim_id = -1) INTO valid_condition;

    IF NOT valid_condition THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Invalid ti_id and/or tim_id combination.';
    END IF;
END

Then create triggers BEFORE INSERT and BEFORE UPDATE, where I simply:

CALL CheckUserPermissions(NEW.ti_id, NEW.tim_id);

1 Answer 1

1

The simplest solution is to remove ti_id from your user_permissions table.

If user_permissions references some tim_id's, and each respective trackable_item_metrics references exactly one ti_id, so you can get the correct trackable_item by joining through trackable_item_metrics to trackable_items.

This means the RI anomaly you described cannot happen.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.