March 6th, 2012, 04:57 PM
Conceptual Desing of deletion rules
delete problem in database design
(my english is bad btw)
i have a "conceptual" problem that i want to solve to all software i develop.
the problem is define conceptually the rules for allow to delete a record considering its relationships.
this is designing a relational database model/schema
usually a basic database model/schema will have tables and relationships between them.
this relationships translate to foreign keys in a database also as constraints (if we want)
a basic rule for deletion is to use relationships to know if that record is necesary to other records,
if so, dont allow its deletion.
in fact a basic mechanism of dabatases is to throw some error if u try to delete a record that is "in use",
which means this id exists in another table.
thats a basic rule which is not good to real software.
Having a record thats already been associated to another record is not always a reason to not let someone delete it.
lets say i have a user and this user has many emails and a user can input PROJECTS to the system
theres 3 entities USER EMAIL PROJECT, and relationship are USER has many EMAIL and USER has many PROYECT.
conceptual rules for deletion
email defines a user, its part of him, if he doesnt exists, then emails should be gone too.
but if a user already added a project to the system, then the user cannot be deleted.
using the basic deletion rule thas uses relationships, it wont allow us to delete user if he has emails,
but we know that emails are part of a user, is just resides in another table.
so to do this, theres some databases to allow us to the define "ON DELETE" which means
"what should be done, to a particular relationship, when a record in this table is deleted"
in the practice, this let us delete another record if "this" record is deleted (if the user is deleted then delete his emails too)
conceptually this allows us to define which other tables define "this" entity, or, are part of it.
in other words, with ON DELETE, having a table user and a separate table for his emails is just a design matter
but at the end email is part of the table user.
so setting relationships (with foreign keys) and ON DELETE we actually set the deletion rules for a system.
While designing a database model/schema i use MER notation,
using the 3 standars models: conceptual, logical and physical model. in simple word, conceptual has the ideas
(my customer can actually read this and understand it), and goin to logic (generated automatically from conceptual model, where, for example, the many to many conceptual relationships are replaced for a real relation database relatonship,
that puts a middle table, etc) and then physical model (generated automatically from logical model) this last model actually targets
a real database managment system like mysql, sqlserver, postgres, etc. and from this model automatically generates the sql code to exectue in the
database to build the schema.
so, the ON DELETE is a DBMS specific feature, that will be in my physical model,
i need a way (graphic) to stablish this on my conceptual model, i think MER notation doenst address this problem,
there no symbol to say "this model is part of this other, and if this doesnt exists (is deleted) the other should be gone too"
i call this associtated models as "part of" like email is part of user, and on user delete should be deleted too.
so the question is , does MER has any way to graphically say this models is part of this other ?
this is no the same as setting a foreing key to be part of the primary key of the associated model,
saying "A has many B and A has many C and model B is part of A and model C is not part of A" means if you want to delete A then A is formed by B too so B should be gone,
but if u want to delete A and theres a C using it then A cant be deleted.
if MER doesnt has a way to conceptually design this, is there another notation that address this problem ?
also (bout designing tools), if MER doesnst allow this, theres a tool that has a way to do this in a conceptual model using any notation ?
(i use powerDesigner but software is not then source of my question)
again sorry for my horribad english.