Relationships matter!

A supportive and understanding partner is a useful thing in any trader's life; but it's not what I refer to in the title!

We are going to look at data relationships and why these things are the very building blocks of what makes a relational database a very viable solution for recording and analysing your trading activities.

Thinking back to our tables you'll remember that we have separated those items which are generic to our trading from those which are specific to our individual trades. In this way we ended up with three tables, the main Trades table and one each for the various Sports and the Strategies employed. We now have to create a relationship between those tables so that Access can collate and extract the information that we need to get meaningful data out of our DB.

We'll dive in and do so, then look at what we've done afterwards. Select 'Database Tools' on the main ribbon, then 'Relationships'. A blank page will appear, so we need to click 'Show tables' to bring up our tables. Add all three to the diagram by clicking. As this is a simple DB arrange them so that Sports is one side of the Trades table, and Strategy the other. (If this were a larger DB the advantage of leaving the 'ID' suffix on the field names in the tables will now become very apparent).

Creating a relationship is easy.... once you've worked out what it is...Select 'SportID' in the Sports table by left clicking, and whilst holding the mouse button down, drag the mouse to the SportID field in the Trades Table. On releasing the mouse button you will see this little dialog box:

Notice that Access has identified the two fields to be joined by the names used, and that I have ticked ' Enforce Relational Integrity'. Click 'Create' and repeat the exercise for 'StratID' from the Strategy table and the Trades table, again ensuring that 'Enforce...' box is ticked. You should then see the following:

That's it! Done and dusted!

But what, exactly, have we done? Basically we have created two 'one to many' relationships between our three tables. If you look at the Sports table you will see the figure '1' at the end of the black line, and the '∞' sign at the other end. In English, for every individual sport in the sport table there can be an infinite number of associated trades in the Trades table. Likewise for strategies. By selecting 'Enforce Relational Integrity' we have ensured that in order to be able to enter a new trade we MUST select both the appropriate sport, and an appropriate strategy. Save the relationship window by closing it, and try to enter a new trade without selecting both a sport and a strategy. You won't be able to!

The DB cannot determine whether or not you have chosen the correct sport and / or the correct strategy, but it sure as hell won't let you not select one, correct or not!

Believe me if you are serious about taking this project further with your own ideas grasping this fundamental concept is essential! Making the correct decisions about how to split and record your data at the outset saves hours of tears and frustration later after you've entered shed loads of data. Trust me, I've got the tee shirt!

To enable you to see what a professional database looks like I suggest you download the free 'Northwind' sample DB from Microsoft. The relationship diagram from this is shown below and you will see how simple our little trading DB is by comparison!

Next we'll start to look at how to extract some interesting facts and figures from our DB.