DB with no relationships, but with SQL queries

[may1hem]

A friend suggested that a good way to manage a database is to not define any relationships in the Relationships window, but instead to create queries using SQL code and define relationships there. Is there any merit in this?

My SQL is not strong and I'm not fully aware of what it's capable of. One thing that comes to mind is referential integrity. Surely this wouldn't prevent someone from deleting a parent record, and this would leave orphans? But if all deletions were done through an SQL query then surely it could be controlled in a way that keeps ref integrity?

In another thread, jflachman wrote: "As far as I can tell, the relationships in the relationships window are only there as a default when creating queries. You can modify those relationships in the query window all you want. You can have no fixed relationships and define them manually every time you create a query. Once your query is designed, changes in the relationships window have no effect. Therefore, since your queries all function, removing the relationships in the relationships window has no effect."

Surely this backs up the case that relationships in the Relationships windows are really unnecessary?

If this is correct then the only benefit of creating relationships would appear to be some ease-of-use and documentation?

Any thoughts?

Thanks,

May

 

[vb6novice]

May,

I am in complete agreement with your friend and jflachman. The only things having the relationships defined in the Relationships window does is pull them in when you create queries. You can write all the SQL you want regardless what is in the relationships window. I can't remeber the last time I actuall used the relationships window.

As far as referential integrity, that is helpful if your programming and user interface design is not refined enough to prevent deleting a parent record. I don't let my users delete records. I let them set a value that will mark the record so that it does not come up in the queries that supply the forms, reports, combo boxes, etc.

Clean up routines that run when the database opens can get rid of records that are no longer deleted with custom designed referential integrity coded in.