August 1st, 2012, 09:33 AM
Database Architecture Question
I've been working on a database application (Access 2007) and have a couple questions regarding architecture. This is, by far, the most complex application I've written yet.
I presently have 5 tables, each with its own unique ID field,
It is possible for the same fault to appear in multiple procedures, and for the same procedure to appear in multiple manuals. I would like to provide users the opportunity to copy and delete records, as well as cross-link records between tables.
Presently, I have join tables between each of the tables listed above,
Editing the relationships between the main tables (by editing contents of the join tables) has gotten quite complicated, as copy and delete actions have a cascading effect on other tables. In my mind, this could be performed efficiently using recursion, but actually getting it to work is another story altogether.
Question #1: Is the architecture I described a "good" way to arrange this data, or is it unnecessarily complicated? (I have a habit of doing things that way.)
Question #2: Is there a "preferred" method of doing this?
Question #3: Would I be better off organizing the tables in a different manner, such as this:
Only 5 tables,
Each table has a key for its content fields, as well as another key representing its parent record in the next higher table. For example,
platformID, vehicleID, vehicleName
vehicleID, manualID, manualName
This seems more straightforward to me, and certainly less complex. If you all would care to provide some professional feedback, I would greatly appreciate it.
August 1st, 2012, 01:01 PM
For a many-to-many relationship you need a join table. No way around that. Thus since many procedures can be contained in many manuals, and there are many faults for many procedures, you need the manuals/procedures and procedures/faults tables.
For platforms/vehicles and vehicles/manuals, are those also many-to-many? If not (a vehicle has one platform and a manual is for one vehicle) then the alternate solution you have is better. Especially if a vehicle must have a platform and a manual must be for a vehicle.
August 1st, 2012, 01:22 PM
Thanks for the reply. The structure is as you described, with many-many relationships not necessarily required for the first couple tables.
Indeed, that was how I originally had it designed. But then when I started writing the navigation/maintenance code, I had to write parallel procedures: one for the first couple tables, the other for the many-many tables. So then I converted them all to many-many tables with the join tables in between.
The more options I consider, the more complicate it gets (eg, user wants to delete a fault, but what if that fault is also associated with another procedure? Or, user wants to delete a procedure, but what if some of it's faults are used by other procedures?).
I'm slowly chugging along...
August 1st, 2012, 02:02 PM
With join tables, cascading deletes should only go into it. Not out of it. So if you delete a fault then it and anything related to it in the procedures_faults should go away. On the other hand deleting the relationship (procedure exists, fault exists, it's just that the fault isn't associated with that procedure anymore) should not affect the procedure or the fault.
For one-to-many it's still unidirectional: deleting a platform means all its vehicles are gone too, but deleting a vehicle does not delete the platform.
If you want to abstract the work out, use stored procedures. That's where they work best: the underlying structures can change but the interfaces to them (eg, DeleteFault and RemoveFaultFromProcedure) will remain the same. Not that the structures should change once set, but the programmer doesn't have to care about that.
For the record, as a programmer I don't like the magic behavior of cascades, but since this isn't a programming forum I'm trying to give the "database administrator" versions of answers.
Also for the record, IMO actually DELETEing stuff isn't good. Things should be marked as deleted, and doing so simply hides it from most interfaces. Primary reason is that a DELETE means anything at all related to the record through a foreign key has to be clobbered too: relationships, logs, histories... The downside is that 99% of queries have to take that flag into account.
August 1st, 2012, 02:26 PM
Wow, more good information. I have a Status field in all my data tables already. When a user selects "Remove" the code is actually changing the status from "Active" to "Deleted", without changing the join tables.
For my own cleanup routines, I have a real Delete function - this actually deletes records and join table relationships.
I can do these operations on a single table now, but the cascading part is becoming quite complicated.