Relational DB vs JOIN

[LeonR]

This may be a really strange question, but why would you use relational tables within a DB when you can query them using INNER JOIN etc? (Assuming common ID's exist obviously)?

 

[jmcilhinney]

Foreign keys enforce the relationship, mainly allowing you to prevent situations where orphan child records are added or left behind after deleting a parent. I don't know whether this is the case or not but I would not be surprised if databases also stored extra information about related tables that allowed them to optimise queries that join on foreign key columns.

 

[LeonR]

Thats what I figured to be honest, as obviously from a code perspective you can still acheive what you want from using JOIN, I will do some research and see if anything else is taking place behind the scenes when defining relationships within the DB itself.

My approach is to keep things as simple as possible generally, so I have not decided whether or not foreign keys are required yet, but obviously you do not want orphans as you say.

Cheers,
Leon

 

[jmcilhinney]

There's no reason not to define foreign keys in the database. Also, if you use a tool to generate a DAL, e.g. the Entity Framework, those foreign keys are used to define relationships between entity classes in your project.

 

[LeonR]

Ok thanks!

In my case I am using mainly stored procedures and then parsing data from a custom class (no SQL queries client side), i've taken this route to make any code changes easier in the future, such as for some unknown reason that it was to be changed to java or perl, maybe a mobile app.... etc etc (who knows!)

I will set my foreign keys to maintain data consistancy

Thanks again!