Are certain table relationships redundant?

[Stormin]

Hi all,

I am designing a new database and trying to do it properly. Just getting my head around table relationships... are ambiguous joins redundant? Do I need to create a relationship in every table wherever a foreign key appears with the parent's parent table primary key?

i.e. in the image attached, do I need the one-to-many relationship between tblAB_Types.BonusID and tblAB_BonusPayment.BonusID, or is it redundant since it is related through the table tblAB_Periods?

Thanks for your help!

 

[Stormin]

So I thought about this for a while before posting and couldn't seem to grasp it. But after writing it out with an example and thinking further I think I have answered my own question.

When using BonusID and PeriodID as a compound key I am essentially mimicing creating a new key such as BonusAndPeriodID. Therefore relating tblAB_Types.BonusID to one half of tblAB_BonusPayment.BonusAndPeriodID is not correct, as it is not the same key.

I think this is right, but let me know otherwise!

 

[CJ_London]

relationships only matter for the control of data, otherwise you are just using them as documentation to auto populate a query in the query builder when the two tables are included.

so if you define a relationship, you also need to define what type of relationship in order for the db to apply rules - the most common is a one to many relationship when a child cannot exist without a parent - i.e enforce referential integrity so you cannot delete a parent record without first deleting the child records.

And the rules that are applied should fit with the requirements of the business and process.

From your attachment, I would not use a multi field primary key, use an autonumber primary key - you can still create a multifield index - use the index option on the ribbon to create it

You are also providing two ways to get from one table to another - generally a nono. Recommend remove the link between types and bonus payment, you can get to it the other way

 

[Stormin]

Thanks CJ, I need to think more along the lines of "These are child records that cannot exist directly without the parent records in this table" rather than "These two field names match!!".

Ideally I would use a single autonumber primary key and a multi-index, and I indeed I have in other areas of the database, however this particular combination of BonusID and PeriodID is a recent evolution from using just PeriodID across the entire database so I had tacked BonusID into all the tables.
Maybe I will split the tables into tblAB_Periods with just PeriodID, and then a new table tblAB_BonusPeriods that match the BonusID with the PeriodID to create a new pkey BonusPeriodID. This is since each BonusID can actually use the same bonus period (and hence PeriodID) which would otherwise cause unnecessary repeat records in tblAB_Periods.

I am getting there, thanks for your help!

 

[gemma-the-husky]

Thanks CJ, I need to think more along the lines of "These are child records that cannot exist directly without the parent records in this table" rather than "These two field names match!!".

As CJ points out, the premise "cannot exist" is not to do with the relationship per se - it's to do with applying Relational Integrity (RI) to the relationship, which is offered as an option in the relationships diagram. Adding RI prevents you being able to have orphan records.

Note that you won't be able to apply RI, if you have records in the table that conflict with this,

Adding RI also means that you can cascade update, or cascade delete. ie deleting the master record also deletes matching child records. FWIW I hate the idea of automatic/cascading deletes. I much prefer to manage my own deletes. If you do have RI in place, then you won't be able to delete a master record if child records exist.

 

[Stormin]

Cheers Dave, I should have mentioned that I have RI enforced on all my relationships with cascade update turned on. I, too, am uneasy with the cascade delete option and never use it, prefering to handle my own deletes (and backups!)

 

[Pat Hartman]

I don't see a Period table. If total AB periods is a junction table that connects bonus to period, you are missing a table.

CJ gave you the correct advice. Make an autonumber primary key for the junction table and then create a unique index that includes the BonusID and PeriodID. Then BonusPayment is a child of the junction table and would have only the reference to the PK of the junction table rather than both IDs. That will eliminate the confusion and you will not even be able to create the pathological relationship.

 

[Mark_]

OK, three steps back so that we can see what you are trying to replicate.

What causes a bonus to be? Is it "Management decides to give you money?", is it "We did good so all employees get money?", it is "I'm the programmer and I decide you should be rewarded for making good espresso?".

Are bonuses one time payments or are they on a schedule?
How is the amount to be paid calculated?
Who has authority to create one or disburse one?
To whom will the bonus be paid? Are there limits or restrictions?

More important, what exact business rules are you trying to replicate?

These kinds of answers will help identify if not only your relationships are proper, but if your tables contain what data is needed.

 

[Stormin]

Pat,

Thanks for the advice. Upon reflection and taking into account everyone's advice I have changed the structure of this section. I should note that this is a re-build of an existing (working) database I had created that requires more functions with slightly different business rules, hence why some sections require careful thought and comparison against how I would have built it from the ground up; instead of just tacking everything on to the end!

An image of the update to the section with all the relevant tables visible is attached. This all looks good to me but if anyone can see anything wrong please let me know.


Mark, thank you for your extended help. However I believe that is goes beyond the scope of my original question and woud be a full database design project meeting
To give you a little background though this uses vehicle sales data from my other database and uses it to compile car dealership bonus volumes and payment totals based on their individual targets for each bonus type and for each period. This is simply a reporting tool and does not control payments or transactions.

 

[CJ_London]

looks a lot better to me

But it is your app, so long as it meets the requirements. With regards Marks comments, they may be beyond the scope of the original question however I would agree these are things you should consider otherwise you are likely to be back for another redesign.

Often newbies only look at a small part of the overall design to solve the immediate problem and consequently suffer from 'mission creep' as things change.

I have over 20 years experience building complex commission/reward solutions for quite small companies to large multinationals - I appreciate your requirement is just the reporting end but I would at least review with your stakeholders (at least the ones who provide the input) their thoughts on the future.

 

[isladogs]

I'm not clear why you need the table on the right of the screenshot or why it would be one to many. If it's one to one, the table is redundant as the description field can go in the parent table.

 

[Stormin]

Hi ridders,

That's true, it is not clear just from the relationship view. BonusName in the parent table is the unique 'database name' for the bonus scheme, e.g. I have "QuarterlyBase" and "QuarterlyAdditional". In the generated reports sent to the dealerships however, these both have the BonusNameFriendly "Quarterly" as if they were one bonus scheme (which they are, they just behave differently and hence need different BonusIDs to differentiate that behaviour).

Thanks CJ, I appreciate your expertise. You are quite right - in fact, the reason that it is going under such a large redesign is to 'future-proof' the system so that any changes to existing or new bonus schemes can be added using existing infrastructure rather than having to need yet another redesign. My initial design was rather short-sighted, but my excuse is that it was based on some assumptions from the original plan which have now been broken. Lesson learned!
This is an internal system for the department I work in so luckily I am intimate with the requirements both present and future

 

[Mark_]

Ah, single payment bonuses for performance during period; non-contractual; employer initiated; Fixed amount? Does this about sum it up?

Just a couple questions though, starting with naming conventions.
Why do all of your tables begin with "TblAB"? I can understand the Tbl (I normally use T_, or T?_ where the ? is a character to represent the specific type of table if needed) but what is the AB for? Similarly in TblAB_Periods, you have MinDate and MaxDate. Are these really the start and end dates for the period?

If you haven't begun working out a naming convention for your database, I would highly recommend doing so. By having the names convey useful information you can often avoid issues later, especially if another is dealing with your code.

As these are performance bonuses, do you have any place to save the metrics relevant to the bonus? something like "Sell 50 sedans with a cost over X during April". Since you are doing the reporting, even if everyone assumes they all know what the rules are often you will be asked to clearly spell them out. This helps a lot when employees get upset that they didn't get something and want to see where it says that. Likewise what field holds to whom the payment was made based of off who's performance?

 

[Stormin]

Some good questions there Mark.

OK, so we are a motor manufacturer that put a lot of new vehicles on the roads through contracts. After the contracts end the vehicles come back to us. We remarket them and sell them to the dealership network at trade prices (which in turn are sold by the dealerships to consumers at retail prices).

Bonuses programmes are in place obviously to incentivise the dealerships to buy more used vehicles from us than other sources. Bonuses are paid on a per-vehicle basis as long as certain requirements are met for the period. If the dealership does not meet the requirements in that period then none of that bonus is paid on any vehicle.
For example if a dealership target was 100 vehicles at £150 per vehicle... 99 vehicles purchased = £0 in dealership bonus, and 100 vehicles = £15k in dealership bonus.

We want the database reporting tool to be able to generate a one-page report per dealership summarising their qualifying purchases, how much bonus they have earned in the period so far, and how many more vehicles they need to purchase to reach the next level (in the example above, it would be very incentivising to outline that just one more purchase could earn you £15k extra).

Now when I originially created this database there were two types of bonuses: a 'target' bonus and 'ladder' bonus. Hence my naming convention was TB = TargetBonus, LB = LadderBonus, AB = AllBonus. The rest of my naming convention is based around the Leszynski naming convention.

The target bonus is a simple "buy X amount of vehicles in Y period" with several targets per dealer, with higher amounts of bonus per vehicle for the higher targets.
The ladder bonus was a complicated accumulator-type bonus with loads of different additional earnings based on which vehicle models the dealer purchased and loads of overlapping periods etc. It was very difficult for the dealerships to keep track of how much bonus they would earn per vehicle in any one month and thus to work out exactly the margin they would make by reselling their vehicles.

This year the ladder bonus was scrapped, the overlapping periods were scrapped, and there was put in place one simple multi-level target bonus with one cyclic period. I started to redesign the database around this. However, one of our brands has yet to catch up with implementing this new bonus scheme and still operates two separate bonus schemes with overlapping periods which needs to be incorporated into the database. Luckily it's not the ladder bonus but it is still a pain in th-- I mean: it still requires some extra development time.

Regarding tblAB_Periods, yes MinDate and MaxDate are the boundaries for each period. However, they are structured 'unique' periods such as 2018-Q1 (quarters), 2018-T1 (trimesters), and 2018-H1 (half-years) and each BonusID will only use one type of period.

Regarding the parameters, yes everything is stored in other tables. The parameters will be changed as new periods come into effect and we set different individual targets and bonus reward amounts. I will not be the end-user of this database so all the parmeter settings will be done in the GUI / userforms created towards the end of the project. For now, I just need the whole thing to work properly with all the differing rules, and indeed the proper structure in place to allow for easy expansion.

---

In other news currently there is one issue that I'm not certain I am solving in the best way. For the catch-up brand's Quarterly bonus there are flat target volumes that apply to every dealership. The first 3 levels are, for lack of a better word, 'overwriting' in that once you reach level 1 the level 1 bonus amount applies to all vehicles; once you reach level 2 the level 2 bonus amount applies to all vehicles (replacing the level 1 bonus amount); up to level 3. However level 4 is 'additional exclusive' in that all vehicles purchased up to level 3 have level 3 bonus applied, but all vehicles purchased at level 4 and above have just the level 4 bonus applied.
For example, let's say that level 2 is 50-74 purchases @ £100pv, level 3 is 75-99 purchases @ £200pv, and level 4 is 100+ purchases @ £300pv.
A dealership purchasing 60 vehicles would be earning £6k (£100pv).
A dealership purchasing 80 vehicles would be earning £16k (£200pv).
A dealership purchasing 120 vehicles would be earning £26.1k (£200pv on 99 vehicles and £300pv on 21 vehicles).

At the moment I have two separate BonusIDs for "QuarterlyBase" (levels 1-3) and "QuarterlyAdditional" (level 4) and tblTB_VolumeTargets_FlatVol set up with BonusPaymentID, MinVol, MaxVol. The idea is that if the dealership volume maxes out the QuarterlyBase then the bonus amount will equal MaxVol of the max level multiplied by that level's bonus amount, PLUS the single level of QuarterlyAdditional minus its MinVol multiplied by the bonus amount. Does that make sense? I'm not sure if there's a better way to do it than to split it into two bonuses and do a custom calculation.
For the example of 120 purchases... (99 * 200) + [(120-99) * 300] = 26100.



Apologies for the wall of text

 

[Mark_]

In you pre-calculate max bonuses and save them, you've got about the quickest way to do this. May need a "Bonus Sequence" to show what group of bonuses your working in though. Then start with the highest MinVol and work down till you hit one that is greater than their volume.

I'd also save the amount they get for all sales below the minimum volume. Then your calculation is easy; [Minimum Volume Bonus Amount] + (Volume - MinVol) * Rate.

This means that you ALWAYS track based on what the current rate is. If it gets applied to previous sales, it is already included. If it doesn't, it is already excluded. You pre-calculate the bonus (including if they want a target bonus tossed in) and just calculate based on what is passed their target.

 

[Stormin]

Thanks for your advice, I'll let you know how it goes