Im designing a database structure and I would like that it's design could prevent erroneous connections.

I'll make a simple example, that I think it will be easy to understand, bellow are the tables:

   Brand - Vehicle - Engine - Fuel Emision for Diesel test
                            - Fuel Emision for Gasoline test
                            - Fuel Emision for Trucks test
                            - Speed test

Let's assume that one Vehicle has one Brand and one Engine, but there are several tipes of tests that you could do to the Vehicle.

How can I guarantee for example that the results of the "Fuel Emision for Trucks test" bellongs to an ID of a truck Vehicle

Is there any design that can hide certain tables to some type of car or make them unconnectable? Or some kind of a check?

  • An XML Schema ought to be able to enforce rules like that. Not especially useful unless your database is abstracted behind a web service or something. – James Snell Mar 5 '16 at 11:12

In Oracle you can create a virtual column based on a function. Your function can contain any business logic you want but should be deterministic and it helps if the function resolves to 0 or 1 for clarity.

Create a user function that returns 1 if the VEHICLE_TEST_TYPE_ID is valid for that kind of vehicle and that kind of test listed for VEHICLE_TEST_TYPE.

Then create a virtual column on Your_Function(Vehicle_id,Vehicle_Test_Type_id) and a constraint that the column = 1 ERD diagram


Your question seems to be: Can I allow or disallow foreign keys based on a value in a database row?

The short answer to that question is: no.

What you want may be achieved in a number of ways (in no particular order):

  1. You can add triggers and/or stored procedures to your database (provided these are supported by your database engine of choice). Whenever a new row is inserted into one of the test tables, have it fail if the type of Vehicle doesn't match up with the test.
  2. Introduce a new set of tables for each type of Vehicle you wish to introduce and have test tables link with these. This will make the structure of your database more complex, especially if you want a particular type of test to be performed on multiple Vehicle types.
  3. You could program it into the business logic of your application.

Note: Using any special functions (like stored procedures or virtual columns) from your database engine of choice is likely to have different syntax, work differently and/or be unavailable in another database engine. Using any of those solutions will effectively lock your application to that database engine. This may not be a problem, but should be considered as part of your overall design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.