What do I gain from specifying relationships in my database?

Question

I am building a project in MS Access 2010. I have previous experience in Oracle. I am reading about MS Access and keep seeing references to table relationships. It looks like a convenient way to assist the average person in data entry and validation and for query building, but I write queries exclusively in SQL mode and enforce data entry for users with forms that have their own validation rules.

Is it really necessary to enforce relationships? It doesn't seem like it really gains me anything at an advanced level, and might actually cause problems for me or someone else who eventually takes over maintenance from me later. I've never used them before and I'm not really seeing a benefit to starting now. Can anyone shed some light on that?

Answer 1

From a person just querying data, then the relationships are not that important. However from an application point of view, they are VERY helpful if not outright important.

For example, you might have a customer’s table, and then say an orders table. The business rule is that you can’t create an order unless you first have a customer. So if you freely write some SQL to add an order without a customer, your update/insert query will NOT work. And if you need to delete a customer, then all orders for that customer can/will automatic delete for you without having to write a complex delete SQL statement. You might for example want to delete all customers older than 5 or 10 years (so they are inactive). When you delete those customers, then you want all orders also deleted. (This is a VERY difficult query to write if you have to delete the child records for each customer. with enforced relations, then all child records will automatic delete for you (enforced cascade delete)).

And it also important from a reporting point of view. If you write a query to display all customers this month and their billing totals then you get one total result. However if you decide that you do NOT want to display/include customers, you might hit just the orders table and get a total amount that way. The problem is without RI, then you might (by accident or even just some user launching the orders form) have entered order information (with a total amount) but NO customer.

Now what happens is when you run the two different reports/quires, you find the total is DIFFERENT! In a complex application as to “why” the two reports are different can take days, or with lots of data even a week to figure out why two reports on monthly sales do NOT agree with each other. If you enforce the business rule that no orders can be entered into the system UNLESS they have a customer, then you eliminate such errors in reporting. You can “say” that you are perfect user of SQL, but with lots of code, lots of forms for data entry, how can you EVER be sure that orders are NEVER entered without a customer. The user during data entry may forget to enter the customer in that order form. And even if you write code in that order form to ENSURE that customer must be selected, maybe YOU during the writing of some SQL by accident insert an order record into the system without a customer. However your monthly customer total report query “assumes” that you have a customer record that you THEN join in the order totals data.

However some reports must just run on the orders data (a monthly summary total does not need to include customers). The problem now is somewhere in the system you have an order record with total data that does not have a customer. The result is different reports and quires on sales total now don’t agree. This is an outright nightmare.

So some bug or error in the application code might occur and result in what is supposed to be relational data now having “orphaned” records. Perhaps your business rules allow entering of orders without a customer assigned, but then your monthly sales report will have to show that fact, or any query that hits the orders table and does not include customers will have to “check” for the possibility in those queries that no customer record yet exits.

The above is only a SIMPLE scratching of the surface of the GAZILLION issues that crop up. So while you might be just creating simple quires on the data, the problem is that data correctly related in the system? The old saying about garbage in = garbage out rings true here.

At the end of the day when you’re SQL quires pulls data with MULTIPLE tables, then you HAVE to make assumptions about that data and its relational integrity (RI). So when you write that query to display customers and their order totals, you ASSUME and drop in the customers table, and then relational join in the orders table. However if orders exist without a customer record, then your query not going to produce the correct values. And worse a report that hits the orders table will now produce different results.

If you enforce RI then no matter what, you cannot enter an order by accident or force without FIRST having created a customer record. If you don’t enforce such rules, then your data will produce incorrect results.

And a typical complex application will have 40 or 70 related tables. And EVERY ONE of those tables is going to have assumptions made as to if parent (or child) record are “assumed” to have been created correctly based on your set of business assumptions.

You might have a tour booking system. Customers might phone up, put down a deposit but NOT yet be booked to a particular tour. If you allow this setup, then your query on customers this month and their booked tour will have to take this into account. However maybe the business rules are that any customer in the system that puts money down MUST ALSO be booked to a tour (and thus you query to grab that information will take this rule into account).

If every query you always made never was to include data from more than one table, then you likely don’t benefit much from enforcing relational data. However the instant you start bundling queries with multiple tables, then you MUST know the assumptions being made about that data before you can write a query. So do you allow customers with a deposit in the system without a tour booking or not? This rule will decide how you must write that query. If RI is enforced, then you can query on a customer “booking” that and you KNOW that it will be attached to a tour event. And same goes if any booking + deposit does not need a booking – but you HAVE to know the assumption made about that data.

So based on assumptions made about the data is the ONLY practical way to create a query to pull data based on those assumptions. And if you enforce RI, then you at least know the data MUST be related and setup based on those assumptions.

At the end of the day? Anyone creating a data base that models a business application and rules without enforcing RI is building a ship without rudder and without a compass.

And exporting data from each table is a NON issue. However if that data is a mess and has orphaned records, then you only wind up exporting a incorrect data model to another database and all of the issues and problems remain.

Answer 2

If you are building queries purely in SQL mode, defining the relationships probably doesn't make any difference for you. The only thing that might be useful is that if you built something, then didn't look at it again for a few months, you would be able to quickly re-acquaint yourself with the relationships conceptually.

For anyone using the access query builder, defining the relationships allows you to quickly add tables to the query while Access automatically builds the proper (GIGO) relationships for the query JOIN. Again, if you are writing in SQL, you probably already do this, so not much help for you in query building.

Bottom Line - it's more of a graphical tool to streamline the query process, at least until you try exporting the tables to a "real" RDBMS, as someone else already mentioned.

Answer 3

You say you have previous experience of Oracle. Did you never define Foreign Key constraints in Oracle? If you did, then that is what you are doing when you define relationships in Access. You can use it for enforcing referential integrity (not allowing you to delete a parent record if child records still exist) or, if you use the cascade delete option, for automatically deleting child records if you delete a parent record. It's a useful backup to cover coding errors where you might have forgotten about possible child records that would otherwise be orphaned if you did not have the relationship (FK) defined.

Answer 4

Relationships helps in preserving data integrity and I agree with your point that if user is entering from access form, probability of errors due to integrity is lesser. But in future if user is moving from MS Access to pure RDBMS, this relationship will definitely will be helpful.

Though objective of relationship is not for migration at later point-in-time, for your case that is one valid reason I could think-of.

Other than that, for MS Access with its own forms relationship may not add specific values.

Answer 5

If its not a requirement for you use case then you don't necessarily have use this. A use case where this could be "required" is in an Order Based Scenario.

Lets say you have a Database that creates and tracks Orders. Each Order can have multiple Lines that are tied to the same Order. But for Normalization purposes, most people would separate these into two separate tables. OrderHead and OrderDetail. You would want to enforce Referential Integrity here to ensure that there is never a child record in OrderDetail that doesn't link back to a Parent Order.

I'm sure that you could prevent things like that without it, but it mainly just enforces it.