CODEWITHSUNDEEP
c#sql-serverentity-frameworkdatabase-design
Dindar
Dindar

Reputation: 3245

Database design for inheritance Business rules

Imagine a big company has a lot of delegation all around the world . You are going to design a system in which Employees on each Office submit their extra-work-hour into the system each day.

We have these tables:

  ----------         --------          -------        --------       ----------     
 |Countries| 1---> * |States| 1--->*  |Cities|1 --->* |Offices|1--->*|Employees|
  ---------          --------         --------       ---------       -----------

The Manager would be able to determine maximum extra-work-hour (business rule ) at each level ( continents , countries , ... ) .

The rules would be inherited from the parent to the child . and child could override that

For instance if the manager determine Country1 maximum extra-work-hour would be 4 then all employees of Country1 their maximum valid extra-work-hour would be 4 and if the manager determine CityXX which is in StateYY and this State is in Country1 Max would be 3 then all Employees of that City(CityXX) their valid extra-work-hour would be 3 .

I hope could get it across . My question is how do you implement such this system which rules would be inherited ?

Edit :

Thank you all . I create those Tables , because those are different entities . Each on of them has different properties and Behavior . This is just a imaginary situation . My real requirement need even more levels and much more entities and business rules.

Upvotes: 2

Views: 389

Answers (5)

yousef ekhtiari
yousef ekhtiari

Reputation: 1

Simplicity is the beauty of life,I agree with gbn you can store the MaxExtraHours at office table so you don't need to bother yourself writing a complex query.anyway if you insist on not changing the structure your query will work as coalesce returns the first non-null expression among its arguments.

Upvotes: 0

A-K
A-K

Reputation: 17090

You can use a materialized path based approach. I have written a canned answer here: Store your configuration settings as a hierarchy in a database

Upvotes: 1

Jon Raynor
Jon Raynor

Reputation: 3892

Building on GBN's solution, one level would be optimal. The hierarchy you describe could be a default precedence, state is 10, city is 20, and so forth. Users could override if needed or if the hierarchical relation is static it could just be coded into a meta data table (RulePrecedence).

Here's a sample query:

Select * from Rule Where [What Rule Is Needed] Order by Precedence

Then you can have as many evaluations/levels as you want per rule and they are are evaluated in the order of the precedence.

I think that would work and the schema would not be that complex.

If there are not that many evaluations, then the query can be simple. If there could be potientially thousands of "overrides" then you may want to something like this to limit the "Rules" that are returned:

Select * from Rule Where Rule.Country = 'US' AND Rule.State = 'MA' And Rule.ID = 'MaxExtraWorkHour' Order By Precedence

Please note these queries are only for example. (Select * is used to save typing only)

Upvotes: 1

gbn
gbn

Reputation: 432431

One option is to store it at one level only so it doesn't "inherit" at all.
Is this really an inheritable property at the city or office level or Employee level? Local legislation (eg EU Working Time Directive) determine it, not a manager.

Another approach is to denormalise it.

  • Each child has two columns: maximumextraworkhour and IsLocalOvertimeRule (bit)
  • Changes in the parent push down via trigger where the child IsLocalOvertimeRule = 0

Upvotes: 1

Robert Iver
Robert Iver

Reputation: 741

My initial thought would be to have a field that holds max extra work hours at each level, and then allow the appropriate people at each level to set that field. Then, in your table joinery, you would join up all your tables and then use COALECSE to get the first field that was not null, starting with the most specific and moving toward the general.

Something like:

SELECT
   COALECSE(o.MaxExtraHours, t.MaxExtraHours, s.MaxExtraHours, c.MaxExtraHours, 4) as MaxExtraWorkHours
FROM
   Countries c
INNER JOIN
   States s on c.ID = s.CountryID
INNER JOIN
   Cities t on s.ID = t.StateID
INNER JOIN
   Offices o on t.ID = o.CityID

The "4" is a default value, but you could just leave it null, or store the default value at each level as well, and then start walking down the defaults after you try the specific fields.

Upvotes: 0

Related Questions