2

In the database i have one base table, named Organization that most of tables has foreign key to that. assume this below ERD:

Database ERD

As you seen Organization table has a column named is_enabled. now if i want to disable the Organization, i should set is_enabled to 1. it's very simple, but the problem is that if i want to prevent all other procedures and functions to use the disabled Organization, i should define trigger on all of the tables that has foreign key reference to Organization table, and this way is bad.

Is there another way to prevent all objects to use disabled Organizations? also for insert or update the data, for example if i have a Organization with org1 title, and if it has a Department with dep1 name, after i disabled org1, i couldn't update dep1 name, and this problems exists for all tables that has reference to Organization.

Rasool Ghafari
  • 4,128
  • 7
  • 44
  • 71
  • In this tables yes, but maybe on other tables exisist two or more column – Rasool Ghafari Aug 26 '17 at 14:58
  • 1
    Eight *hundred* tables with a foreign key reference to Organization seems like a whole lot, is what I'm trying to say. Especially eight hundred tables with only a few columns. ??? – Bob Jarvis - Слава Україні Aug 26 '17 at 15:01
  • I make a simple ERD to recognize the problem, also each table have it's own columns – Rasool Ghafari Aug 26 '17 at 15:03
  • Please clarify what is meant by 'use'. What should happen when records referring to disabled organizations are a) changed b) queried? – wolfrevokcats Aug 26 '17 at 15:07
  • I mean that if everyone want's to insert a record, for example into Person table with the value of organization that is disabled, throw an exception and prevent actions like that – Rasool Ghafari Aug 26 '17 at 15:10
  • I can see no other way but using triggers for that. What's wrong with them? If you wanted those organizations to behave as if they didn't exist you could use Fine Grained Access Control (FGAC) - but this is not the case. – wolfrevokcats Aug 26 '17 at 15:24
  • 1
    Business logic such as "you're not allowed to update a disabled organization" should not be implemented in a trigger. A better solution would be to have a procedure which you'd call to perform the desired operation, and which would make all the needed consistency checks. Best of luck. – Bob Jarvis - Слава Україні Aug 27 '17 at 15:29

1 Answers1

0

If you want to stop users from changing the data for disabled organisations, this should be in your code - either in stored procedures or application code. You could have some logic or restrictions in the user interface to prevent changes to disabled organisations (the actual implementation depends on your requirements).

The other question I have (as mentioned by Bob Jarvis) in the comments, is why do you have 800 tables with an organisation ID? This doesn't seem to be normalised.

For example, why does a person link directly to the organisation table? Could they link to the department table, which has its own link to the organisation?

bbrumm
  • 1,342
  • 1
  • 8
  • 13
  • By the way that you said, i should change many places in the code ether in stored procedures. but i don't want to do this, because large amount of change. And for your question, as you in the comments, i created a simple ERD to avoid complexity in the question. actual models are the bigger and have more columns. – Rasool Ghafari Aug 28 '17 at 04:28
  • What are the alternatives? If a database and system is well designed then it should be easy to make the change in the right place. If it's too hard to prevent users from updating data for a disabled organisation, then you should refactor your code to make it easier to update. Short term pain now for long term gain :) – bbrumm Aug 28 '17 at 22:10