Never actually learn proper about database design and today my colleague given me a crash course on database design. all things start with data deletion, there is one important data that dependent by many other tables, i was using sql delete statement to delete each of them in correct order and one day my colleague's module also depend to that important data so that is the interesting event happened, when i remove my data my colleague depended table are automatically gone together! without any carefully delete statement!
so how did my colleague did it? foreign key is the answer, but i did foreign key before but without using the ON DELETE and ON UPDATE option, therefore to auto delete a depended row just need to add "ON DELETE CASCADE".
i would like also stating the key characteristic of a good database design.
1) auto increment index is not a good reference id, any changes of the order will have nightmare to establish the relationship
2) for index and id column, do not use the simple word of "index", "idx" or "id" if not when joining multiple table with same column hell will break loose
3) use relationship to constraint your data, to maintain data integrity
4) use another "link" table for multi to multi relationship
5) the standard design: atomic data (no retyping) and all non key data should depend on the primary key, if not they should belong to other table
No comments:
Post a Comment